SQL Swiss Army Knife #2.1 - Scripting Logins - Updated

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
06/02/2015 - Latest update on the AdaptiveIndexDefrag procedure v1.6.1. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

11/03/2015 - Updates to Maintenance plan deployment scripts available HERE

17/05/2015 - The SQL Swiss Army Knife Series Index

SQL Swiss Army Knife #2.1 - Scripting Logins - Updated

Rate This
  • Comments 0

EDIT (20-09-2012) - Added several scripting options; does not use SQL Server 2000 system tables anymore.
EDIT (11-01-2013) - Fixed issue with permission syntax depending on version (2005 vs. 2008/2012).
EDIT (18-03-2013) - Fixed issue with undeclared variable in cursor; Added options for access.

Hello all,

Here is another posting focusing on SQL scripts that may help on everyday DBA tasks, following the series "SQL Swiss Army Knife". This is another example of a script I've been using as a "local machine" bit of a wider DR strategy, in this case generating all logins and their respective server roles. Note that this does not deal with CERTIFICATE_MAPPED_LOGIN and ASYMMETRIC_KEY_MAPPED_LOGIN types.
These are the options available:

  • All users:
    EXEC usp_logins
  • One user, All DBs:
    EXEC usp_logins '<User>'
  • One user, One DB:
    EXEC usp_logins '<User>', '<DBName>'
  • All users, One DB:
    EXEC usp_logins NULL, '<DBName>'

Its output may simply be saved onto a .sql file and will resemble this:

usp_logins

So I hope some of you will find useful.

Until next time!

Download code here: usp_logins.sql

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.

 
Social Media Sharing
|
Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post