Solved

Permissions for query of db security (audit)

Posted on 2011-03-10
6
235 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
  • 4
  • 2
6 Comments
 
LVL 39

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 39

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help Required 3 96
T-SQL: Do I need CLUSTERED here? 13 43
How to use three values with DATEDIFF 3 24
SQL Count issue 24 16
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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