Here is the correct one:
select * from sys.database_permissions would give the correct permissions for all the users and all the objects.
Here is a sample query to list all the permissions;
i got this from the following url:
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx
The below one isn't the correct one, as per MSDN:
http://msdn.microsoft.com/en-us/library/ms186233.aspx
"Returns one row for each table privilege that is granted to or granted by the current user in the current database"
select * from sys.database_permissions would give the correct permissions for all the users and all the objects.
Here is a sample query to list all the permissions;
i got this from the following url:
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
The below one isn't the correct one, as per MSDN:
http://msdn.microsoft.com/en-us/library/ms186233.aspx
"Returns one row for each table privilege that is granted to or granted by the current user in the current database"
use dbname
go
select * from information_schema.TABLE_PRIVILEGES
GRANTOR GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
dbo user1 db1 schema1 table1 DELETE NO
dbo user1 db1 schema1 table1 DELETE NO
dbo user2 db2 schema2 table2 INSERT NO
No comments:
Post a Comment