How to retrieve login details with object level permissions for all database in SQL Server 2008?

Hi,

Please provide the query to retrieve login details with object level permissions in SQL Server 2008.

Thanks & Regards,
Chandra
Chandra Mohan KanithiPrincipal Consultant - DatabaseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ankit PareekOnline MarketingCommented:
Hey you can get login access details for all databases in SQL server 2008 and access for all user type here is the way
https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2
0
Chandra Mohan KanithiPrincipal Consultant - DatabaseAuthor Commented:
The above query displaying only 'public'  user name details only.

We need all logins details with object level permissions.
0
Yashwant VishwakarmaSQL DBACommented:
try this:

SELECT  
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],      
    [Role] = null,      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],      
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],  
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',      
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],      
    [PermissionState] = perm.[state_desc],      
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]                  
JOIN
    --All objects  
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]
0
Yashwant VishwakarmaSQL DBACommented:
You can also try with the below t-sql
=========================================
DECLARE @db_id INT = Db_id () 

SELECT @@servername 'TargetServer', 
       Db_name()    'TargetDB', 
       Getdate()    'Date' 

-- Section to identify the users and the respective database roles to which the user is associated with 
SELECT 'EXEC ' + Db_name(@db_id) 
       + '..sp_addrolemember @rolename  = ''' 
       + sdp_role.name + ''', @membername = ''' 
       + sdp_usr.name + '''' 'Query To Add DB Users To DB Roles', 
       sdp_usr.name          'DBUser', 
       sdp_role.name         'RoleName', 
       * 
FROM   sys.database_role_members sdrm 
       JOIN sys.database_principals sdp_role 
         ON ( sdrm.role_principal_id = sdp_role.principal_id ) 
       JOIN sys.database_principals sdp_usr 
         ON ( sdrm.member_principal_id = sdp_usr.principal_id ) 
WHERE  sdp_usr.name <> 'dbo' 
ORDER  BY sdp_usr.name, 
          sdp_role.name 

-- Section to display database - object level privilege to be granted to db users explicitly 
SELECT 'GRANT ' + sdp.permission_name + ' TO ' 
       + db_usr.name COLLATE sql_latin1_general_cp1_ci_as 
       'Query to Grant DB rights', 
       db_usr.name                                        'User/Role Name', 
       Object_name(sdp.major_id, Db_id())                 'ObjectName', 
       sdp.permission_name, 
       sdp.grantee_principal_id, 
       * 
FROM   sys.database_permissions sdp 
       JOIN sys.database_principals db_usr 
         ON ( db_usr.principal_id = sdp.grantee_principal_id ) 
WHERE  grantee_principal_id <> 0 
       AND class_desc = 'DATABASE' 

-- Section to display database - object level privilege to be granted to db users explicitly 
SELECT 'GRANT ' + permission_name + ' ON ' 
       + Object_name(sdp.major_id, @db_id) + ' TO ' 
       + db_usr.name COLLATE sql_latin1_general_cp1_ci_as 
                                          'Query to Grant explicit rights', 
       -- 'GRANT ' + sdp.permission_name  + db_usr.name    'User/Role Name', 
       db_usr.name                        'User/Role Name', 
       Object_name(sdp.major_id, Db_id()) 'ObjectName', 
       sdp.permission_name, 
       sdp.grantee_principal_id, 
       * 
FROM   sys.database_permissions sdp 
       JOIN sys.database_principals db_usr 
         ON ( db_usr.principal_id = sdp.grantee_principal_id ) 
WHERE  grantee_principal_id <> 0 
       AND class_desc = 'OBJECT_OR_COLUMN' 

Open in new window

DECLARE @db_id INT = Db_id ()

SELECT @@servername 'TargetServer',
       Db_name()    'TargetDB',
       Getdate()    'Date'

-- Section to identify the users and the respective database roles to which the user is associated with
SELECT 'EXEC ' + Db_name(@db_id)
       + '..sp_addrolemember @rolename  = '''
       + sdp_role.name + ''', @membername = '''
       + sdp_usr.name + '''' 'Query To Add DB Users To DB Roles',
       sdp_usr.name          'DBUser',
       sdp_role.name         'RoleName',
       *
FROM   sys.database_role_members sdrm
       JOIN sys.database_principals sdp_role
         ON ( sdrm.role_principal_id = sdp_role.principal_id )
       JOIN sys.database_principals sdp_usr
         ON ( sdrm.member_principal_id = sdp_usr.principal_id )
WHERE  sdp_usr.name <> 'dbo'
ORDER  BY sdp_usr.name,
          sdp_role.name

-- Section to display database - object level privilege to be granted to db users explicitly
SELECT 'GRANT ' + sdp.permission_name + ' TO '
       + db_usr.name COLLATE sql_latin1_general_cp1_ci_as
       'Query to Grant DB rights',
       db_usr.name                                        'User/Role Name',
       Object_name(sdp.major_id, Db_id())                 'ObjectName',
       sdp.permission_name,
       sdp.grantee_principal_id,
       *
FROM   sys.database_permissions sdp
       JOIN sys.database_principals db_usr
         ON ( db_usr.principal_id = sdp.grantee_principal_id )
WHERE  grantee_principal_id <> 0
       AND class_desc = 'DATABASE'

-- Section to display database - object level privilege to be granted to db users explicitly
SELECT 'GRANT ' + permission_name + ' ON '
       + Object_name(sdp.major_id, @db_id) + ' TO '
       + db_usr.name COLLATE sql_latin1_general_cp1_ci_as
                                          'Query to Grant explicit rights',
       -- 'GRANT ' + sdp.permission_name  + db_usr.name    'User/Role Name',
       db_usr.name                        'User/Role Name',
       Object_name(sdp.major_id, Db_id()) 'ObjectName',
       sdp.permission_name,
       sdp.grantee_principal_id,
       *
FROM   sys.database_permissions sdp
       JOIN sys.database_principals db_usr
         ON ( db_usr.principal_id = sdp.grantee_principal_id )
WHERE  grantee_principal_id <> 0
       AND class_desc = 'OBJECT_OR_COLUMN'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.