Solved

Permissions for query of db security (audit)

Posted on 2011-03-10
6
243 Views
Last Modified: 2012-05-11


I want to allow a user to be able to view security information for ALL user databases within a SQL Server Instance (2005).

If I create a login on the instance and add it to the sysadmin role, I can use the following query,
substituting in any valid database name on the instance where you see <dbname> to get the information I need.

use  <dbname>
SELECT UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, [desc] = sp.type_desc FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
UNION SELECT p.name as UserName, p.type_desc as UserType, pp.name as LoginName, pp.type_desc as [desc] FROM sys.database_role_members roles JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
order by UserName asc

This works.  However, I do not want to give the login sysadmin privileges.  
It seems that the login would only need select privileges on the three system views at the database level, but that doesn't appear to be enough.
Also, I'd really rather not have to administer things at the database level (e.g. create a user in each database vs. creating the login on the instance and assigning it to a server role)   Databases come and go, so I wouldn't want to have to manually add permissions every time a new database is added.

I'm trying to determine the minimum privileges that are required to execute this query so that it will work against any of the databases on the instance and return the information for ALL users.

Any help would be greatly appreciated.

Thanks.
0
Comment
Question by:web-dba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 35099015
You can use the following to determin/assign only the right you need to grant to the user:

--at db level:
EXEC sp_helpdbfixedrole
EXEC sp_dbfixedrolepermission 'db_securityadmin'

--at server level:
EXEC sp_helpsrvrole
EXEC sp_srvrolepermission 'securityadmin';
0
 

Author Comment

by:web-dba
ID: 35189411
Thanks for the reply, but I'm trying to query the security info for all databases on the fly.  So I can't add permissions at the db level.  Is there a specific permission I can apply at the server level without giving up the full privileges of sysadmin?
0
 

Author Comment

by:web-dba
ID: 35189470
Clarification - I can set server level permissions and also I can add specific permissions for the system databases.  But user databases are added on the fly and I want to be able to view the security info for all databases, even ones that are newly created, without adding specific permissions at the database level. Thanks.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 40

Expert Comment

by:lcohan
ID: 35201163
<<But user databases are added on the fly and I want to be able to view the security info for all databases, even ones that are newly created, without adding specific permissions at the database level.>>

For that I would add DDL triggers and query the DDL audit tables or use SQL Dashboards reports that come free from Microsoft.

DDL triggers:
DDL_SERVER_LEVEL_EVENTS
http://msdn.microsoft.com/en-us/library/ms186582(SQL.90).aspx
DDL_AUTHORIZATION_SERVER_EVENTS
http://msdn.microsoft.com/en-us/library/ms180671(SQL.90).aspx
DDL_GDR_SERVER_EVENTS
http://msdn.microsoft.com/en-us/library/ms186418(SQL.90).aspx

SQL reports - open your SSMS, connect to the SQL server where you want to see all schema changes then right click the server name, select Reports - > Standard Reports -> Schema Change History
0
 

Accepted Solution

by:
web-dba earned 0 total points
ID: 35722540
Thanks for the suggestions.  None of them really allowed me to do what I was trying to do...I'm going to look for a different approach.
0
 

Author Closing Comment

by:web-dba
ID: 35759452
Thanks for the suggestions.  None of them really allowed me to do what I was trying to do...I'm going to look for a different approach.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
semaphore timeout period has expired 1 33
SQL Server sp_executesql / Change Tracking Version() results into variable 4 30
Report 8 27
SQL Server Error: 4060 8 33
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question