locked
Need to get the information of execute permissions on all objects in all databases for a sql login . RRS feed

  • Question

  • Hi,

    I need the information of all sql logins in the server which has execute permissions on what wat objects irrespective of the databases.

    for ex: consider abc is an sql login in a server which has 150 Databases. i need to know what are all the objects abc has execute permission in all databases.  Someone please help me.

    Thanks

    Wednesday, November 28, 2012 10:59 AM

Answers

  • CREATE TABLE #perms (login_name sysname NULL,
                                             dbuser_name sysname NOT NULL,
                                             object    nvarchar(400) NOT NULL)
    INSERT #perms
    EXEC sp_MSforeachdb '
    SELECT suser_sname(u.sid), u.name,
                 db_name() + ''.'' + s.name + ''.'' + o.name
    FROM     ?.sys.database_permissions dp
    JOIN     ?.sys.database_principals u
         ON dp.grantee_principal_id = u.principal_id
    JOIN     ?.sys.objects o ON dp.major_id = o.object_id
    JOIN     ?.sys.schemas s ON o.schema_id = s.schema_id
    WHERE    dp.permission_name = ''EXECUTE''
        AND    u.type = ''S''
    ORDER BY o.name'
    SELECT * FROM #perms ORDER BY login_name, object
    go
    DROP TABLE #perms

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Shulei Chen Wednesday, December 5, 2012 7:30 AM
    • Marked as answer by Shulei Chen Thursday, December 6, 2012 9:07 AM
    Wednesday, November 28, 2012 10:51 PM

All replies

  • Wednesday, November 28, 2012 11:50 AM
  • Hi Udhayan,

    The following does the trick:

    IF EXISTS (SELECT     *
                            FROM         tempdb.dbo.sysobjects
                            WHERE     id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]')) DROP TABLE [tempdb].[dbo].[SQL_DB_REP]; 
    GO
    CREATE TABLE [tempdb].[dbo].[SQL_DB_REP](
    	[Server] [varchar](100) NOT NULL,
    	[DB_Name] [varchar](70)  NOT NULL,
    	[User_Name] [nvarchar](90)  NULL,
    	[Group_Name] [varchar](100) NULL,
    	[Account_Type] [varchar](22)  NULL,
    	[Login_Name] [varchar](80) NULL,
    	[Def_DB] [varchar](100) NULL
    ) ON [PRIMARY]
        INSERT      
         INTO            [tempdb].[dbo].[SQL_DB_REP] 
         Exec sp_MSForEachDB      'SELECT  CONVERT(varchar(100), SERVERPROPERTY(''Servername'')) AS Server, 
    ''?'' as DB_Name, 
    usu.name u_name  ,case	when (usg.uid is null) then ''public''
    					else usg.name
    				   end as Group_Name
    ,Case When usu.isntuser=1 then ''Windows Domain Account'' WHen usu.isntgroup = 1 then ''Windows Group'' 
         when usu.issqluser = 1 then ''SQL Account'' When usu.issqlrole = 1 then ''SQL Role'' End as Account_Type
                      ,lo.loginname
                      ,lo.dbname as Def_DB
    from
    				   [?]..sysusers	usu left outer join
    					([?]..sysmembers mem inner join [?]..sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
                       left outer join master.dbo.syslogins  lo on usu.sid = lo.sid
            where
    				   (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1) and
    				   (usg.issqlrole = 1 or usg.uid is null)' 
    SELECT * FROM [tempdb].[dbo].[SQL_DB_REP]


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, November 28, 2012 12:11 PM
    Wednesday, November 28, 2012 12:11 PM
  • Hi Basit,

    Thanks for your query. Im getting a result set, which gives me all logins based on standard and custom database roles in the server, Like i was able to get the logins with ddladmin in all dbs. we have given execute permissions explicitly to some SPs in some DBs to some sql logins. I want to get that information. Please help me.

    thanks

    Wednesday, November 28, 2012 12:57 PM
  • CREATE TABLE #perms (login_name sysname NULL,
                                             dbuser_name sysname NOT NULL,
                                             object    nvarchar(400) NOT NULL)
    INSERT #perms
    EXEC sp_MSforeachdb '
    SELECT suser_sname(u.sid), u.name,
                 db_name() + ''.'' + s.name + ''.'' + o.name
    FROM     ?.sys.database_permissions dp
    JOIN     ?.sys.database_principals u
         ON dp.grantee_principal_id = u.principal_id
    JOIN     ?.sys.objects o ON dp.major_id = o.object_id
    JOIN     ?.sys.schemas s ON o.schema_id = s.schema_id
    WHERE    dp.permission_name = ''EXECUTE''
        AND    u.type = ''S''
    ORDER BY o.name'
    SELECT * FROM #perms ORDER BY login_name, object
    go
    DROP TABLE #perms

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Shulei Chen Wednesday, December 5, 2012 7:30 AM
    • Marked as answer by Shulei Chen Thursday, December 6, 2012 9:07 AM
    Wednesday, November 28, 2012 10:51 PM
  • I'm starting a page on the SQL Server Wiki to provide queries of this sort. See http://social.technet.microsoft.com/wiki/contents/articles/15180.effective-database-engine-permissions.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, January 4, 2013 6:11 PM