Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Generating SQL Logins\users and permissions for a database Expand / Collapse
Author
Message
Posted Wednesday, November 25, 2009 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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?


Post #824521
Posted Friday, November 27, 2009 9:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, November 27, 2009 10:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 »

Add to briefcase

Permissions Expand / Collapse