Script to add or restore a user to all 50 store procedures

Question

Script to add or restore a user to all 50 store procedures

tabrez.test

SSC-Addicted

Points: 459
More actions
January 10, 2010 at 12:36 am

#215310

Hi
when i have restored database from production to UAT .as production users will be different for all store procedures., so i need to add all UAT users to 50 store proc.
is there any Script to restore all Existing Users of UAT to the newly restored database .
or
any script to add one user to all 50 store procedures at a time.

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

You have to build your script to add the users to the restored database. You can build a script by querying the sys.logins table.

To grant access, you should create a role - grant the access to that role and then add the users to the role in that database. When the database is restored, you just need to add your UAT users to that role.

The easier way to do that is to grant execute privileges to the role on that schema:

GRANT EXECUTE ON schema::dbo TO role;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Jeffrey Williams-493691 (1/10/2010)
You have to build your script to add the users to the restored database. You can build a script by querying the sys.logins table.
To grant access, you should create a role - grant the access to that role and then add the users to the role in that database. When the database is restored, you just need to add your UAT users to that role.
The easier way to do that is to grant execute privileges to the role on that schema:
GRANT EXECUTE ON schema::dbo TO role;

I agree with Jeff on this. Not only is it easier, it is more consistent and recommended. Continued administration of the database becomes much simpler when using the roles in this manner.

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

Steve Jones - SSC Editor SSC Guru Points: 736232 More actions · Answer 3

This is one of the main reasons you use roles. So you don't mess with this every time you add a user.

Phil Factor SSC-Insane Points: 20244 More actions · Answer 4

Leaving aside the issue of what type of database principals you'd assign (but I agree entirely with the use of roles) here is the sort of code I'd use to assign, or revoke various permissions to database principals. (alter to suit!)

[font="Courier New"]DECLARE @DatabasePrincipals VARCHAR(MAX)

DECLARE @command VARCHAR(MAX)

SELECT

@command='', @DatabasePrincipals='Fred, Joe, Sanji, Fredrico'

SELECT

@command=@command+'GRANT EXECUTE ON ['+[Name]++'] TO '+@DatabasePrincipals+'

'

FROM

sysobjects

WHERE

xtype IN ('P', 'FS', 'FN')

AND [NAME] NOT LIKE 'dt_%'

EXECUTE (@command)

[/font]

Best wishes,
Phil Factor