SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query to get permissions for a user on all databases


query to get permissions for a user on all databases

Author
Message
ForumUser3
ForumUser3
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 113
I want to monitor rights change on certain users with windows authentication and sql server authentication. I am planning to automate this task by running queries against catalog views and saving the results in tables and generating email alerts if any permissions changed for any user on any database.

example:

I have these five windows users (Wuser1, Wuser2, Wuser3, Wuser4, Wuser5) that are part of a group called MyWUsers. They are set to have membership = public, Server roles = public and User Mapping = public to certain databases.

Another user AdUser (user type =SQL Server) has membership and Server roles set to public and sysadmin, User mapping set to db_owner and public on certain databases.

A service account (user type = windows) has membership and server role set to public and user mapping set to db_owner and public on certain databases.


Please provide a sample query that I can use to get their memberships, server roles, use mapping, default schema on all databases on db instance on sql server 2008.

Thank you.

Forum UserCool
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9042 Visits: 600
Hi,

I suggest another approach in SQL Server 2008.
Please try using Audits. They are under Security in SQL Serfver Management Studio. It takes just a few minutes to create. Then you can script them as you can script any SQL Server object
Create a New Audit, Give it a location, accept the default name or give it any other name.
On a server or database level create a new Server or Database Audit Specification.
Select Audit Action Type. As an example I have selected
Database_Role_Member_Change_Group
Database_Object_Permission_Change
Database_Principal_Change_Group
Then I have enebled Audit and Audit Specification by right-clicking and selecting Enable.
As I test I have added a user to Master database and thenI have made him and then removed him from db_datawriter role. This has been recorded in a log. To view log, righ-click the Audit and selct View Audit Logs.

I have got something like this:

Date 10/1/2013 5:57:21 PM
Log Audit Collection (Audit-20131001-135447)

Event Time 17:57:21.7858782
Server Instance Name <my server instance name>
Action ID DROP MEMBER
Class Type ROLE
Sequence Number 1
Succeeded True
Permission Bit Mask 0
Column Permission False
Session ID 66
Server Principal ID 272
Database Principal ID 1
Target Server Principal ID 0
Target Database Principal ID 0
Object ID 1
Session Server Principal Name <my domain name>
Server Principal Name <my domain name>
Server Principal SID 0x150000052100019818780122381183047672310502264300
Database Principal Name dbo
Target Server Principal Name <the user name for the person permissions changed>
Target Server Principal SID 0x16123417675321371476413335254015494208143
Target Database Principal Name <the user name for the person permissions changed>
Database Name master
Schema Name
Object Name db_datawriter
Statement EXEC sp_droprolemember N'db_datawriter', N'<user name>'
Additional Information
File Name <file path i have specified when creating the audit>.sqlaudit
File Offset 1024

Message


Regards,
Yelena Varshal

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search