I was working on Permission for SQL Server for Audit Purpose. Here is the script:
-- Server Level Permissions
Select sy.name,type_desc,
Case When is_disabled = 1 then 'Yes' Else 'No' End as 'IS_Login_Disabled',
Case When isntname = 1 then 'Yes' Else 'No' End as 'IS_NTName',
Case When isntgroup = 1 then 'Yes' Else 'No' End as 'IS_NTGroup',
Case When Sysadmin = 1 then 'Yes' Else 'No' End as 'IS_Sysadmin',
Case When securityadmin = 1 then 'Yes' Else 'No' End as 'IS_Securityadmin',
Case When setupadmin = 1 then 'Yes' Else 'No' End as 'IS_Setupadmin',
Case When processadmin = 1 then 'Yes' Else 'No' End as 'IS_Processadmin',
Case When diskadmin = 1 then 'Yes' Else 'No' End as 'IS_Diskadmin',
Case When dbcreator = 1 then 'Yes' Else 'No' End as 'IS_DBCreator',
Case When bulkadmin = 1 then 'Yes' Else 'No' End as 'IS_Bulkadmin'
from sys.syslogins sy
inner join sys.server_principals sp on sp.sid = sy.sid
Where sy.Name not like '##%'
Order by type_desc desc
-- Database Level Permissions
This sp is source code for sp_helplogins and commented out the last part to get necessary informations:
Create procedure exec dbo.sp_helplogins_rpt --- 1996/08/12 14:34
@LoginNamePattern sysname = NULL
AS
set nocount on
declare
@exec_stmt nvarchar(3550)
declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int
declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)
declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)
declare
@DBOptLoading int --0x0020 32 "DoNotRecover"
,@DBOptPreRecovery int --0x0040 64
,@DBOptRecovering int --0x0080 128
,@DBOptSuspect int --0x0100 256 ("not recovered")
,@DBOptOffline int --0x0200 512
,@DBOptDBOUseOnly int --0x0800 2048
,@DBOptSingleUser int --0x1000 4096
------------- create work holding tables ----------------
-- Create temp tables before any DML to ensure dynamic
CREATE TABLE #tb2_PlainLogins
(
LoginName sysname collate database_default NOT Null
,SID varchar(85) collate database_default NOT Null
,DefDBName sysname collate database_default Null
,DefLangName sysname collate database_default Null
,AUser char(5) collate database_default Null
,ARemote char(7) collate database_default Null
)
CREATE TABLE #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)
---------------- Initial data values -------------------
select
@RetCode = 0 -- 0=good ,1=bad
,@CountSkipPossUsers = 0
---------------- Only SA can run this -------------------
if (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
select @RetCode = 1
goto label_86return
end
---------------------- spt_values ----------------
-------- 'D'
select @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'
select @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'
select @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'
select @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'
select @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'
select @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'
select @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'
--------------- Cursor, for DBNames -------------------
declare ms_crs_10_DB
Cursor local static For
select
name ,status ,sid
from
master.dbo.sysdatabases
OPEN ms_crs_10_DB
----------------- LOOP 10: thru Databases ------------------
--------------
WHILE (10 = 10)
begin --LOOP 10: thru Databases
FETCH
next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID
IF (@@fetch_status <> 0)
begin
deallocate ms_crs_10_DB
BREAK
end
-------------------- Okay if we peek inside this DB now?
IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (@c10DBStatus & @DBOptSingleUser > 0)
begin
select @Int1 = count(*)
from sys.dm_exec_requests
where session_id <> @@spid
and database_id = db_id(@c10DBName)
IF (@Int1 > 0)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end
IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin
--select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end
--------------------- Add the User info to holding table.
select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.sys.sysusers u
,sys.server_principals l
where
u.sid = l.sid' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
,sys.server_principals l
where
u1.sid = l.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
EXECUTE(@exec_stmt)
end --loop 10
--------------- Populate plain logins work table ---------------
INSERT #tb2_PlainLogins
(
LoginName
,SID
,DefDBName
,DefLangName
,AUser
,ARemote
)
select
loginname
,convert(varchar(85), sid)
,dbname
,language
,Null
,Null
from
master.dbo.syslogins
where
@LoginNamePattern is null
or name = @LoginNamePattern
or loginname = @LoginNamePattern
-- AUser
UPDATE #tb2_PlainLogins --(1996/08/12)
set
AUser = 'yes'
from
#tb2_PlainLogins
,#tb1_UA tb1
where
#tb2_PlainLogins.LoginName = tb1.LoginName
and #tb2_PlainLogins.AUser IS Null
UPDATE #tb2_PlainLogins
set
AUser =
CASE @CountSkipPossUsers
When 0 Then 'NO'
Else '?'
END
where
AUser IS Null
-- ARemote
UPDATE #tb2_PlainLogins
set
ARemote = 'YES'
from
#tb2_PlainLogins
,master.dbo.sysremotelogins rl
where
#tb2_PlainLogins.SID = rl.sid
and #tb2_PlainLogins.ARemote IS Null
UPDATE #tb2_PlainLogins
set
ARemote = 'no'
where
ARemote IS Null
------------ Optimize widths for plain Logins report ----------
select
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
, isnull (max(isnull (datalength(DefDBName) ,9)) ,9)
)
,@charMaxLenLangName =
convert ( varchar
, isnull (max(isnull (datalength(DefLangName) ,11)) ,11)
)
from
#tb2_PlainLogins
---------------- Print out plain Logins report -------------
--EXEC(
--'
--set nocount off
--
--
--select
-- ''LoginName'' = substring (LoginName ,1 ,'
-- + @charMaxLenLoginName + ')
--
-- ,''SID'' = convert(varbinary(85), SID)
--
-- ,''DefDBName'' = substring (DefDBName ,1 ,'
-- + @charMaxLenDBName + ')
--
-- ,''DefLangName'' = substring (DefLangName ,1 ,'
-- + @charMaxLenLangName + ')
--
-- ,AUser
-- ,ARemote
-- from
-- #tb2_PlainLogins
-- order by
-- LoginName
--
--
--Set nocount on
--'
--)
------------ Optimize UA report column display widths -----------
select
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA
------------ Print out the UserOrAlias report ------------
EXEC(
'
set nocount off
select
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')
,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')
,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')
,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3
Set nocount on
'
)
----------------------- Finalization --------------------
label_86return:
IF (object_id('#tb2_PlainLogins') IS NOT Null)
DROP Table #tb2_PlainLogins
IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA
Return @RetCode -- sp_helplogins
Drop table #temp
Create table #temp
(LoginName Varchar(500),
DBName Sysname,
UserName Varchar(500),
UserOrAlias Varchar(500))
Insert into #temp
Exec sp_helplogins_rpt
Select identity(int,1,1) ROWID,* into #temp2 from #temp
Delete from #temp2
Where ROWID in
(
Select ROWID
from
(
Select *,ROW_Number() Over(Partition by LoginName,DBName Order by
Case When UserName in (ListUsers...)
then 2 else 1 End)as Seq
from #temp2
)Z
Where Z.Seq = 1)
Select distinct LoginName,DBName
,STUFF(( Select distinct ',' + UserName
from #temp2 t Where t.DBName = tt.DBName and t.LoginName = tt.LoginName
Order by ',' + UserName FOR XML PATH('')), 1, 1, '')
from #temp2 tt
Where LoginName not like '##%'
--Object Level Permissions
Drop table #temp
Create Table #temp
( DBName Varchar(50),
[Type] Varchar(100),
[Schema] Varchar(10),
object Varchar(500),
[User] Varchar(100),
Permission Varchar(20)
)
--Run for all Databases
Insert into #temp
Select 'DBNAME',
CASE WHEN o.type = 'P' THEN 'Stored Procedure'
WHEN o.type = 'TF' THEN 'Table Function'
WHEN o.type = 'FN' THEN 'Scalar Function'
WHEN o.type = 'U' THEN 'Table'
WHEN o.type = 'V' THEN 'View'
WHEN o.type = 'SQ' THEN 'Service Queue'
ELSE o.type
END AS [Type],
s.name as [Schema], o.name as [Object],
pr.name as [User], pe.permission_name as Permission
from sys.database_permissions pe
left join sys.database_principals pr
on pe.grantee_principal_id = pr.principal_id
join (select [object_id] as [id], [name], type, schema_id, 1 as [class] from sys.objects
union
select [service_id] as [id], [name] COLLATE SQL_Latin1_General_CP1_CI_AS [name],
'Service', '0', 17 as [class] from sys.services union
select [service_contract_id] as [id], [name],
'Service Contract', '0', 16 as [class] from sys.service_contracts union
select [message_type_id] as [id], [name],
'Message Type', '0', 15 as [class] from sys.service_message_types) o
on pe.major_id = o.id and pe.class = o.class
left join sys.schemas s on o.schema_id = s.schema_id
where pr.name <> 'guest' and pr.name <> 'public'
order by o.type,o.name,pr.name,s.name
Select * from #temp
Where [User] = 'UserName'
Order by DBName
Permission for SQL Server
Posted - 2010-05-23 : 12:38:29
