Category Archives: User Management

Certificate Based Application Roles

Posted on November 29, 2010 by David Levy

What if I told you that you could sign an assembly that your Windows application uses with a certificate, load that certificate into SQL Server and then define rights on that user-assembly combination. The perfect world where a user would have different rights based on the application they are running but still act under their Windows identity. The perfect world where things work just the way they should. Continue reading →

Posted in User Management | Tagged , | 1 Comment

How Do I Kill All Sessions from a Windows Group?

Posted on April 14, 2010 by David Levy

The stored procedure I am posting today will kill all sessions for users that are members of a given domain group. I use this stored procedure to keep ad-hoc (write down Access) users out of the way of nightly builds on my data warehouse. Continue reading →

Posted in Security, User Management, Utilities | Tagged | Leave a comment

How Can I Tell if a Windows Login has Rights to My Server?

Posted on April 12, 2010 by David Levy

This is a great stored procedure to use for things like server dashboards where you only want people to see the dashboard for servers that they have access to without granting them all the rights that would go with the dashboard. Continue reading →

Posted in Security, User Management, Utilities | Tagged | 5 Comments

How Do I Identify Invalid or Unneeded Logins?

Posted on April 7, 2010 by David Levy

I was looking at one of my servers and was caught off-guard by how many individual users had been granted rights. To make it worse I recognized a few user names as former coworkers. Clearly time for some house cleaning. Here is the script I came up with to do the analysis. Continue reading →

Posted in User Management, Utilities | Tagged , , , , | 5 Comments

Get Users By Connection

Posted on April 5, 2010 by David Levy

Here is some TSQL to get who is connected to a SQL 2005 server by connection type, including a filter to focus on the Dedicated Administrator Connection (DAC). Continue reading →

Posted in User Management | Tagged , , , | 1 Comment

Stored Procedures to Store and Get Database Users with All Permissions

Posted on December 31, 2009 by David Levy

Introduction Traditionally when doing a restore, moving a database, etc. a DBA would simply go into Management Studio and use the Generate Scripts wizard to script all logins and permissions. This approach can take several minutes and as I recently … Continue reading →

Posted in Security, User Management, Utilities | Tagged , , , , | 3 Comments