Permission for SQL Server - SQL Server Forums

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.