Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry, I'm a security noob:

I've been asked to provide write a script that grants our users that belong to some yet unidentified domain group SELECT access to a table called "NewTable1." To identify the global group, I was told to check out "ReportingTable1" and see who (which domain group) has SELECT access to that table and then grant the same group access to NewTable1.

So, in SSMS (2008 R2), I script ReportingTable1 making sure that the option to script permissions was first enabled. When I inspect the generated scripts for ReportingTable1, I only see VIEW DEFINITION rights granted to various domain groups.

Understanding that one might have rights to see the defintiion of an object but not necesarily the object itself, I realize that having VIEW access does not necessarily mean that one will have SELECT access.

Q: Why don't I see SELECT accesss granted when I clearly have select access?

I realize that for a different db, my local db when I am the admin, that I must have implied SELECT rights on all of the tables simply because I belong to the admin group. Therefore, belonging to a role must also give me access rights associated with that role, yet I am unable to determine what those rights are. Are they implied and well known for all of the built-in roles?

Obviously, I have a lot to learn. I expect flames and suggestions to buy a book and read.

share|improve this question
add comment

migrated from stackoverflow.com Oct 25 '11 at 12:56

This question came from our site for professional and enthusiast programmers.

2 Answers

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html

share|improve this answer
add comment

You can run a script to see exactly what rights are set up in a database (http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/08/28/quick-2005-2008-script-to-export-permissions.aspx):

SELECT 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' 
     ELSE dp.state_desc  
   END  
     + ' ' + dp.permission_name + ' ON ' + 
   CASE dp.class 
     WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']' 
     WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']' 
     WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']' 
   END  
     + ' TO [' + USER_NAME(grantee_principal_id) + ']' + 
   CASE dp.state_desc 
     WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' 
     ELSE ';'  
   END  
   COLLATE DATABASE_DEFAULT 
FROM sys.database_permissions dp 
  LEFT JOIN sys.all_objects o 
    ON dp.major_id = o.OBJECT_ID 
WHERE dp.class < 4 
  AND major_id >= 0 
  AND grantee_principal_id <> 1; 

It doesn't necessarily give you the rights per user but you would be part of one of the groups where GRANT SELECT is specified.

Read more about roles at:

Server roles

Database roles

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.