Search This Blog

Thursday, December 4, 2008

Script to get Login's Server and Database level permissions

We have two levels for login

Server Level
Database Level

We can further divide them into Fixed roles and Permissions for a single login as: 

Server Level Permissions
Server Level Fixed Roles
Database Level Permissions
Database Level Fixed Roles

I developed a script to get such details in current environment or on current instance for all databases. Let me know if you find any issue in it.

below scripts returns following attributes as output.

LoginID: ID for database Level/Server Lever Login
Type_Descr: Login Type, SQL User/ Windows User/ Windows Group
Create Date:
Modify Date:
Class_Descr: Description of class on which permission exist, Database/Schema/Object OR Column etc.
Permission_Name: Permission description that is given to login, it contains both Database/Server Roles and Permissions
State_Desc: Grant/Deny/Revoke
DB: it contains DB name Or Server name, for which login is created
P_Type: it is flag P=Permission and F=Fixed role like db_owner, db_readonly etc.



Declare @SQL varchar(500)
            ,@Counter int
            ,@Total int
            ,@DB_Name varchar(50)
            ,@Server varchar(50

Set @Server = @@ServerName
Create Table #DBPermissions
(      LoginID int
      ,LoginName varchar(255)
      ,Type_desc varchar(255)
      ,Create_Date DateTime
      ,Modify_Date DateTime
      ,class_desc varchar(255)
      ,Permission_name varchar(255)
      ,State_desc varchar(255)
      ,DB varchar(50)
      ,P_Type varchar(1)

select identity(int,1,1) id,name,database_id into #DBs from sys.databases

Set @Total = @@RowCount

Set @Counter = 1

While @Counter<=@Total
      Select @DB_Name = name From #Dbs where id = @Counter

      -- Database Permissions
      Set @SQL = 'select Logins.Principal_ID,, Logins.Type_desc, Logins.Create_Date, Logins.Modify_date, P.class_desc, P.Permission_name, P.State_Desc, ''' + @DB_Name + ''',''P'' from ' + @DB_Name + '.sys.database_principals Logins Left Outer Join ' + @DB_Name + '.sys.database_permissions P On P.grantee_principal_id = Logins.Principal_ID          where Logins.type not in (''A'',''R'',''C'',''K'') And P.Permission_name is not null'
      Insert into #DBPermissions
      Exec (@SQL)

-- Database Fixed Role
      Set @SQL = 'select member_principal_id,M.Name , M.type_Desc , M.Create_Date, M.Modify_date, '''',, '''', ''' + @DB_Name + ''', ''F'' from ' + @DB_Name + '.sys.database_principals Roles Inner join ' + @DB_Name +.sys.database_role_members DRoles on Droles.role_principal_id = Roles.Principal_ID And Roles.type  in (''A'',''R'',''C'',''K'') Inner join ' + @DB_Name + '.sys.database_principals M On M.Principal_ID = Droles.member_Principal_ID And M.type  not in (''A'',''R'',''C'',''K'')'

      Insert into #DBPermissions
      Exec (@SQL)

      Set @Counter = @Counter + 1


-- Server Logins Permission
Insert Into #DBPermissions
select SLogins.Principal_ID,, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date, P.Class_Desc, P.Permission_name, P.State_Desc,@Server,'P'
from sys.server_principals SLogins
Inner Join sys.server_Permissions P on P.grantee_principal_id = SLogins.Principal_ID
where SLogins.type not in ('R','C','K')

-- Server Fixed Roles
Insert Into #DBPermissions
select Roles.member_principal_id,M.Name, SLogins.Type_desc, SLogins.Create_Date, SLogins.Modify_date,'',, '', @Server, 'F'
from sys.server_principals SLogins
Inner join sys.Server_Role_Members Roles On roles.role_principal_id =Logins.Principal_ID And SLogins.type in ('R','C','K')
Inner join sys.server_principals M On  roles.member_principal_id = M.Principal_ID     And M.type not in ('R','C','K'


Drop Table #DBPermissions

Drop Table #DBs





Anonymous said...

Script does not work.
Fails with err.
Msg 102, Level 15, State 1, Line 38
Incorrect syntax near 'DRoles'.
Msg 105, Level 15, State 1, Line 59
Unclosed quotation mark after the character string ')


Drop Table #DBPermissions

Drop Table #DBs


Anonymous said...

There are two things wrong with this.

1. Missing single quote in the Database Fixed Role section, which causes the entire script parse to fail.

2. The table alias specified in the join within the Server Fixed Roles section is incorrect. Should be 'SLogins' but is specified as 'Logins'. This does not cause the script parse to fail, but does cause the script execution to fail.

Wilko said...

Very nice script.
A valuable addition would be to add the actual name and type of the object on which a permission was given for example OBJECT_OR_COLUMN; usp_something; Stored Procedure