SQL Server List of db_owner granted permissions
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]
|
Out put:
Rolename Objectname Objecttype Execute Select Insert Update Delete References
public dbo.dt_verstamp003 P GRANT NULL NULL NULL NULL NULL
public dbo.dtproperties U NULL GRANT GRANT GRANT GRANT GRANT
public dbo.Inquiry U NULL GRANT GRANT GRANT GRANT GRANT
public dbo.sp_AddInquiry P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InquiriesByDay P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InquiriesByUserId P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InquiriesByYearAndMonth P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InquiryByClientKey P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InquiryByUserID P GRANT NULL NULL NULL NULL NULL
public dbo.sp_InsertInquiry P GRANT NULL NULL NULL NULL NULL
|