Generate login account scripts for a specific database

Question

Post reply

Generate login account scripts for a specific database

SQL ORACLE

One Orange Chip

Points: 27807
More actions
May 4, 2011 at 9:33 pm

#249910

For some purposes, I would like to generate a script for all login accounts used for one specific database.
Does anyone have it?
Many thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

See if this article helps.

http://sqlblog.com/blogs/eric_johnson/archive/2008/09/30/scripting-sql-server-logins.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

Thank you so much for your input. The script you mentioned is for all logins. I would like to have logins only for one specific user database.

Could you help?

Thanks.

Lowell SSC Guru Points: 323504 More actions · Answer 3

i think if you connect to a specific database and run this it might do what you are after:

select 'EXEC sp_help_revlogin ' + name

from sys.database_principals

where type_desc IN ('SQL_USER','WINDOWS_USER')

and principal_id > 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

Lowell (5/6/2011)
i think if you connect to a specific database and run this it might do what you are after:
select 'EXEC sp_help_revlogin ' + name
from sys.database_principals
where type_desc IN ('SQL_USER','WINDOWS_USER')
and principal_id > 4

And if you know the database in question, you could use a 3-part naming convention here, dbname.sys.database_principals. This is often handy if you're trying to generate the script through PowerShell or the like. It allows you to connect to master and then build all the scripts for all DBs.

K. Brian Kelley
@kbriankelley