Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Permission for SQL Server

Author  Topic 

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-23 : 12:38:29
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








tanksoldier
Starting Member

4 Posts

Posted - 2010-05-27 : 12:51:07
I tried running this against my development server because it's EXACTLY what I need and got multiple error messages. We're running SQL Server 2005:

quote:
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'exec'.
Msg 137, Level 15, State 2, Line 30
Must declare the scalar variable "@LoginNamePattern".
Msg 137, Level 15, State 2, Line 266
Must declare the scalar variable "@LoginNamePattern".
Msg 156, Level 15, State 1, Line 302
Incorrect syntax near the keyword 'INSERT'.
Msg 137, Level 15, State 2, Line 321
Must declare the scalar variable "@LoginNamePattern".
Msg 178, Level 15, State 1, Line 486
A RETURN statement with a return value cannot be used in this context.
Msg 102, Level 15, State 1, Line 508
Incorrect syntax near ')'.
Msg 2714, Level 16, State 1, Line 530
There is already an object named '#temp' in the database.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-27 : 12:54:32
Can you try running Part by Part?
Go to Top of Page

tanksoldier
Starting Member

4 Posts

Posted - 2010-05-27 : 14:51:00
Everything except the "server" level portion returns multiple errors.

Was this intended to run on SQL Server 2005?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-28 : 14:57:10
Yes it is intended to run in SQL 2005 or higher.
Go to Top of Page
   

- Advertisement -