Script to Retrieve Security Information SQL Server 2005 and above

In my working environment I’ve asked to write a script to find security information which should return Server Logins, Database Logins and object level permissions. I’ve written that and I’m sharing this to you since this could be helpful to you.

Works With

  • SQL Server 2005
  • SQL Server 2008 and
  • SQL Server 2008 R2

Script to find server level logins and role assigned

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
          WHEN b.securityadmin=1 THEN 'securityadmin'
          WHEN b.serveradmin=1 THEN 'serveradmin'
          WHEN b.setupadmin=1 THEN 'setupadmin'
          WHEN b.processadmin=1 THEN 'processadmin'
          WHEN b.diskadmin=1 THEN 'diskadmin'
          WHEN b.dbcreator=1 THEN 'dbcreator'
          WHEN b.bulkadmin=1 THEN 'bulkadmin'
          ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'

Script to find database users and roles assigned

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

Script to find Object level permission for user databases

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table

Posted

in

by

Comments

14 responses to “Script to Retrieve Security Information SQL Server 2005 and above”

  1. Andreas Etelk avatar
    Andreas Etelk

    Nice, efficient code. Thank you!

  2. Pavan K avatar
    Pavan K

    Is it possible to know who are all accessed the DB, what were the queries they have ran? It should give the results from the DB creation time?

    1. VidhyaSagar avatar
      VidhyaSagar

      @Pavan — This is frequently asked question. There is no column in SQL which will tell you when database is last accessed, you can either run a profiler or take the db to offline or readonly mode then the required team or user will get in touch with you. Yes you can get the queries using dmvs however this will give you the queries ran from the last SQL Server restart it will give you all the queries from db created date.

  3. DredWes avatar
    DredWes

    Thanks for the to the point code.

  4. Daxesh avatar
    Daxesh

    Very useful sript. I am using this (and one for SQL 2000) to collect all login/user information from many production server into centralized server.

    Only one correction I have to make is,
    replace

    USER_NAME(b.role_principal_id) AS AssociatedRole

    with

    (select x.Name from ?.sys.database_principals x where x.Principal_id=b.role_principal_id) AS AS AssociatedRole

    Reason for this is, when you run sp_MSforeachdb, it run under master database and USER_NAME function will always return value from master database only. (or you can add USE ? at the top of the script.

  5. RA avatar
    RA

    Thanks for the script. I had database with space between the names.

    I modified to use square brackets:

    FROM [?].sys.database_principals a
    LEFT OUTER JOIN [?].sys.database_role_members

    and

    from [?].sys.database_permissions join [?].sys.sysusers U
    on grantee_principal_id = uid join [?].sys.sysobjects

  6. Garry Houghton avatar
    Garry Houghton

    Slick code, thanks man!

  7. Eristoff avatar
    Eristoff

    Thanks for this useful code !!

  8. pavan avatar
    pavan

    Need a Script to take the backup of database users. bcoz we are refreshing Dbs from Production to test..?????????

    1. VidhyaSagar avatar

      @Pavan — Will be posting it soon

  9. Della avatar
    Della

    Question on 1st script, “Script to find server level logins and role assigned”. Isn’t it possible to have >1 Server Role assigned to a Login? If so, this approach would not return all of the Server Roles assigned.

    1. VidhyaSagar avatar

      It should work even if more than one server role is assigned.

Leave a Reply to Looking for script to retrieve user/group permission info on DB2 9.X – dBforums Cancel reply

Your email address will not be published. Required fields are marked *