SQLServer equivalent to dba_role_privs, dba_user_privs, dba_object_privs ???

0 pts.
Tags:
Oracle
SQL Server
Is there a SQLServer equivalent to being able to retrieve internal application specific metadata that to the users/roles v.s. object privs (select, insert, update, delete, execute, etc.) ? In oracle it's achieved by forming SQL selecting data from the appropriate "dba_" views. In SqlServer this can be done interactively through MS enterpriseMngr, not what I'm looking for. But this tells me the data exists within the internal metamodel. Is this info stored and available to me, the DBA, from within SQlServer ? I'm looking for the names of selectable objects (views/tables) that I can manipluate to produce directable reports to me, as the DBA; as I would do in Oracle. Thanks in advance.
ASKED: February 14, 2006  2:06 PM
UPDATED: May 15, 2008  8:07 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The version of SQL Server that you are running will tell you which objects you need to look at. There were lots of changes between SQL 2000 and SQL 2005.

In SQL 2000 you can use this SELECT statement
<pre>select
user_name(p.grantor) as GRANTOR
,user_name(p.uid) as GRANTEE
,db_name() as TABLE_CATALOG
,user_name(o.uid) as TABLE_SCHEMA
,o.name as TABLE_NAME
,case p.action
when 26 then ‘REFERENCES’
when 193 then ‘SELECT’
when 195 then ‘INSERT’
when 196 then ‘DELETE’
when 197 then ‘UPDATE’
when 224 then ‘EXECUTE’
else ‘test’
end as PRIVILEGE_TYPE,
o.xtype
,case
when p.protecttype = 205 then ‘NO’
else ‘YES’
end as IS_GRANTABLE,
‘grant ‘ + case p.action
when 26 then ‘REFERENCES’
when 193 then ‘SELECT’
when 195 then ‘INSERT’
when 196 then ‘DELETE’
when 197 then ‘UPDATE’
when 224 then ‘EXECUTE’
else ‘test’
end + ‘ on [' + user_name(o.uid) + '].[' + o.name + '] to [' + user_name(p.uid) + ']‘
from
sysprotects p,
sysobjects o
where
(p.protecttype = 204 or /*grant exists without same grant with grant */
(p.protecttype = 205
and not exists(select * from sysprotects p2
where p2.id = p.id and
p2.uid = p.uid and
p2.action = p.action and
p2.columns = p.columns and
p2.grantor = p.grantor and
p2.protecttype = 204)))
and p.action in (26,193,195,196,197,224)
and p.id = o.id

and 0 != (permissions(o.id)

)

order by table_name</pre>

In SQL 2005 you can use this much shorter SELECT statement.

<pre>select sys.schemas.name ‘Schema’, sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc,
state_desc + ‘ ‘ + permission_name + ‘ on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']‘ COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
order by 1, 2, 3, 5</pre>

If you want to view the metadata about the tables and columns you can use the INFORMATION_SCHEMA views in SQL 2000 (INFORMATION_SCHEMA.tables and INFORMATION_SCHEMA.columns). In SQL 2005 you would want to use the sys.tables and sys.columns DMVs.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following