SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 List database users and their database roles
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2007
Posting Yak Master

184 Posts

Posted - 05/06/2013 :  13:40:28  Show Profile  Reply with Quote
Hello All,

I wanted to list the active (ONLY enabled) logins and their database roles.

The script below gives me that information however it shows orphan users also. The column with is_disabled = NULL is orphan user.

Is there a way to get only the enabled logins and their database roles?


-- CREATING A TEMP TABLE TO LOAD THE DATABASE USERS WITH DATABASE ROLES ; 
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]([DBNAME] [SYSNAME] ,[USERNAME] [SYSNAME] ,[DB_OWNER] [VARCHAR](3) ,[DB_ACCESSADMIN] [VARCHAR](3) ,[DB_SECURITYADMIN] [VARCHAR](3) , [DB_DDLADMIN] [VARCHAR](3) ,[DB_DATAREADER] [VARCHAR](3) ,[DB_DATAWRITER] [VARCHAR](3) ,[DB_DENYDATAREADER] [VARCHAR](3) ,[DB_DENYDATAWRITER] [VARCHAR](3) ,[DT_CREATE] [DATETIME] NOT NULL,[DT_UPDATE] [DATETIME] NOT NULL,[DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE()) ) ON [PRIMARY] 
GO 
INSERT INTO    [TEMPDB].[DBO].[DB_ROLES] EXEC SP_MSFOREACHDB '     SELECT      ''?'' AS DBNAME,       USERNAME,      MAX(CASE ROLENAME WHEN ''DB_OWNER''         THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,        MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN ''   THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,        MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN''  THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,        MAX(CASE ROLENAME WHEN ''DB_DDLADMIN''   THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,        MAX(CASE ROLENAME WHEN ''DB_DATAREADER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,        MAX(CASE ROLENAME WHEN ''DB_DATAWRITER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,      MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,        MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,        CREATEDATE,      UPDATEDATE,        GETDATE()        FROM (        SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE             FROM                        .DBO.SYSMEMBERS A   JOIN .DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID                         JOIN .DBO.SYSUSERS C ON A.GROUPUID = C.UID                          )S                   GROUP BY USERNAME, CREATEDATE, UPDATEDATE          ORDER BY USERNAME'

select a.*, b.is_disabled from [TEMPDB].[DBO].[DB_ROLES] a
LEFT JOIN sys.server_principals b
ON a.USERNAME = b.name
WHERE DBNAME IN ('DB1Main', 'DB2', 'DBBilling') AND (DB_OWNER = 'YES' OR DB_ACCESSADMIN = 'YES' OR DB_SECURITYADMIN = 'YES'
OR DB_DDLADMIN = 'YES' OR db_datareader = 'YES' OR db_datawriter = 'YES' OR DB_DENYDATAREADER = 'YES' OR DB_DENYDATAWRITER = 'YES') AND a.USERNAME <> 'dbo'


Thanks,
-PL

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/07/2013 :  00:39:52  Show Profile  Reply with Quote
just add a WHERE condition as

WHERE b.is_disabled IS NOT NULL

In that case it may be better to convert left join to inner join so that NULL contain doesnt even occur

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 05/07/2013 00:40:53
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 05/10/2013 :  05:52:22  Show Profile  Reply with Quote
You can also do select * from sys.syslogins (This gives users with access to DB and their roles)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000