Generating SQL Logins\users and permissions for a database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Generating SQL Logins\users and permissions...

Generating SQL Logins\users and permissions for a database

Rate Topic

Display Mode

Topic Options

Author

Message

allanroodT

Posted Wednesday, November 25, 2009 7:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 19, 2010 1:51 AM
Points: 4, Visits: 90

Hi There,

Is there any script or SP that can generate user permissions for database\tables\SP's to be reapplied?

Im trying to find a way of creating a single script that will output all the users and their permissions for a database so I can put it back later ( just like when you use management studio to generate a script for users and permissions and just like Idera's SQL Permissions application ).

Here is my scenario:

I am asked to restore a database that already exists ( overwrite the database ) from one server to another.

1)Before I restore, I have to script out the current users and their permissions on the database.
2)I restore the database ( RESTORE DATABASE [TEST] .......WITH REPLACE )
3)I reapply the user permissions I scripted out earlier ( as they were before the restore )

What I am looking for is a script or SP that takes all the current users in a database and generates a script based on their current permissions on a database ( like GRANT EXEC TO [user] ON [dbo.StoredProcedure], EXEC sp_grantdbaccess..... )

Does anyone know of a way to do this? Unsure

Post #824521

support.sql

Posted Friday, November 27, 2009 9:59 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 2:07 PM
Points: 70, Visits: 32

Hi

Review this article, it contains your requirements.

http://www.sqlservercentral.com/scripts/Security/66129/

SNM

Try Free Microsoft SQL Server Data Collector & Performance Monitor.

http://www.analyticsperformance.com

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

Post #825688

support.sql

Posted Friday, November 27, 2009 10:05 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, December 02, 2009 2:07 PM
Points: 70, Visits: 32

You can use SMO Object Model to implement backup script, database restore and restore scripting.

SNM

Try Free Microsoft SQL Server Data Collector & Performance Monitor.

http://www.analyticsperformance.com

@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

Post #825689

« Prev Topic | Next Topic »

Permissions