Script to find database users and roles assigned
running on SQL-SERVER using T-SQL Script
Tthis script will help you find all database users and the roles associated accordingly
Before You Proceed We recommend you to read our Disclaimer below
The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.
In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.
Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.
Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName
SQL-Server Scripts
- Find Backup History.
- Find Restore History.
- Find CPU Usage in the last Hour
- Last ran query along with the time it was executed
- Currently running queries
- Top 50 High CPU Utilization Cached Plan
- longest running query using T-SQL
- Find database files location
- Find index fragmentation
- Find Memory Usage
- Finding who ran a DELETE STATEMENT.
- Finding the number of transactions per minute
- Object Level Permissions
- Script to find object level permissions on a database with Create definition.
- Script to find server level logins and roles assigned
- Script to find database users and roles assigned
- Script to find Object level permission for user databases
- Script to find Orphaned users in an entire Instance of SQL Server
- SQL INSTANCE AND VERSION
- FIND WAITS IN AN SQL INSTANCE
- View wait stats for all block processes on SQL Server