List of db_owner granted permissions

Question

Post reply

List of db_owner granted permissions

gorden 93576

SSC Veteran

Points: 296
More actions
July 28, 2011 at 10:55 am

#242225

I am an application developer (Oracle) new to SQL Server. I'm looking to get a list of all object/user permissions I've granted. If this is possible, please provide syntax.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

http://technet.microsoft.com/en-us/library/ms189612(SQL.90).aspx

db_owner has all the rights of these subordinate roles as well...

basically the db_owner role can do anythign to the database..back it up, drop it, or create/change/destroy anything within that database as well.

--db_accessadmin

--db_backupoperator

--db_datareader

--db_datawriter

--db_ddladmin

EDIT--doh! i didn't read deep enough.

search the script section for "Script roles" and "Script permissions"; there are a whole bunch of scripts there.

this is just one of them, that gets permissions per object:

SELECT Rolename,

[Objectname],

[Objecttype],

[Execute],

[Select],

[Insert],

[Update],

[Delete],

[References]

FROM (SELECT P.Name AS Rolename,

(S.Name + '.' + O.Name) AS [objectname],

O.TYPE AS [objecttype],

Dp.Permission_name,

Dp.State_desc

FROM Sys.Database_permissions Dp

JOIN Sys.Database_principals P

ON P.Principal_id = Dp.Grantee_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 P.TYPE = 'R'

AND P.Principal_id < 16000

AND Dp.Class = 1

UNION

SELECT P.Name AS Rolename,

(S.Name) AS [objectname],

'Sch',

Dp.Permission_name,

Dp.State_desc

FROM Sys.Database_permissions Dp

JOIN Sys.Database_principals P

ON P.Principal_id = Dp.Grantee_principal_id

JOIN Sys.Schemas S

ON Dp.Major_id = S.[schema_id]

WHERE P.TYPE = 'R'

AND P.Principal_id < 16000

AND Dp.Class = 3) P

PIVOT

(MAX(State_desc)

FOR Permission_name IN ( [EXECUTE],[SELECT],[INSERT],[UPDATE],[DELETE],[REFERENCES] ) ) AS Pvt

ORDER BY Rolename,

[Objectname]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gorden 93576 SSC Veteran Points: 296 More actions · Answer 2

Thanks, Lowell for your reply/advice.

I found what I was looking for in the Scripts section.

This did it...

sp_helprotect @username = 'User ID'