S3OLV April 2011

Comments: No Comments
Published on: April 5, 2011

Are you ready to learn again?  The Las Vegas SQL Users Group is ready to have our April meeting.  The meeting is to be held April 14, 2011 at 6:30 PST.

This month we have a BI presentation lined up and some top notch talent to present to us.  Erika Bakse will be teaching some excellent tips and tricks revolving around MDX and SSRS.

Abstract:
Reporting Services is a powerful tool that can make designing reports a snap…most of the time. But every once in a while you have to format a report very specifically, and the data just isn’t available in the form you need it to do that.  Enter Clever Queries!  Learn about how to use named sets, dummy members, and other MDX tricks to craft your data query in a way that allows you to conquer the trickiest report layouts.

This month, Erika will be presenting in person at the Learning Center.  But don’t be dismayed if you cannot attend in person.  We will be broadcasting the event via Livemeeting once again.

To access the meeting via livemeeting, just follow these steps:

To join the meeting? Follow these steps:

  1. Copy this address and paste it into your web browser:
    https://www.livemeeting.com/cc/UserGroups/join
  2. Copy and paste the required information:
    Meeting ID: F598S6
    Location: https://www.livemeeting.com/cc/UserGroups

We hope to see you there (either in person or via livemeeting).

SQL Server Role Membership

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: April 5, 2011

How well do you know the security in your SQL instances?  Do you know who has sysadmin level permissions?  SQL Server provides a few methods for you to find out who is a member of which roles at the server level.

For those that like to point and click, you can always navigate through the GUI (SSMS) to determine which users or groups have been granted access to the sysadmin fixed server role.  For those that want something a bit faster, you can use a script to return this information for you.  Just as with most things TSQL, there are numerous different ways of writing this script.  Here are some of those methods.

Verifying Server Role membership

[codesyntax lang=”tsql”]

[/codesyntax]

With this script, I am querying the sys.server_role_members and sys.server_principals views.  For simplicity sake, I am also using the SUSER_NAME() function to derive the role name.  Note that I threw in a union all to get back the ‘Public’ group membership.  The public group is a special group that does not appear when querying the sys.server_role_members view – but everybody is a member.

An Alternative

[codesyntax lang=”tsql”]

[/codesyntax]

This one is quite simple as well.  Note that I am not employing the use of the SUSER_NAME function but have used another join in its place.  I am also only interested in adding the public role at this time to SQL Users, Windows Users and groups that are not disabled.  That information in the where clause is optional and is present to demonstrate the ability to quickly pare down the results.

Another Option

This is really the easiest of the three queries.

[codesyntax lang=”tsql”]

[/codesyntax]

I am still employing the union statement to populate the public role.  Notice the difference in the first half of the query though.  I am simply using the SUSER_NAME function for both principal_ids being retrieved from the server_role_members view.  This is a little easier to follow and write.  Performance considerations put this last query as the most efficient on my systems with the first query shared being a close second.

All of these will return your group memberships quickly and in a manner that is quickly understandable (names instead of numbers).  The use of a query such as these would be a stepping stone into auditing the permissions that are in place on your server.  It is also great to quickly validate who has sysadmin access and to use that to confirm that the account should have sysadmin access.

page 1 of 1

Calendar
April 2011
M T W T F S S
« Mar   May »
 123
45678910
11121314151617
18192021222324
252627282930  

Welcome , today is Sunday, June 16, 2019