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
LoginName:
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.
Script:
Begin
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
Begin
Select @DB_Name = name From #Dbs where id = @Counter
-- Database Permissions
Set @SQL = 'select Logins.Principal_ID, Logins.name, 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, '''', Roles.name, '''', ''' + @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
End
-- Server Logins Permission
Insert Into #DBPermissions
select SLogins.Principal_ID, SLogins.name, 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,'', SLogins.name, '', @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')
SELECT * FROM #DBPERMISSIONS ORDER BY 1
Drop Table #DBPermissions
Drop Table #DBs
End
