Solved

Permissions for query of db security (audit)

Posted on 2011-03-10
6
250 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 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