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

• tabrez.test

  SSC-Addicted

  Points: 459

  More actions

  January 10, 2010 at 8:17 am

  #138073

  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.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  January 10, 2010 at 11:27 am

  #1101535

  This script will allow you to add a user to all the stored procedures that you choose at once:

  USE YourDatabase

  DECLARE @Namevarchar(150),

  @sqlvarchar(500)

  DECLARE Perm_Cursor CURSOR FOR

  SELECT [Name]

  FROM sysobjects

  WHERE xtype ='P' AND [NAME] NOT LIKE 'dt_%'

  OPEN Perm_Cursor

  FETCH NEXT FROM Perm_Cursor

  INTO @Name

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @sql = 'GRANT EXECUTE ON [' + @Name + '] TO YourUserName'

  EXEC(@SQL)

  FETCH NEXT FROM Perm_Cursor

  INTO @Name

  DEALLOCATE Perm_Cursor

  END

  CLOSE Perm_Cursor

  You could add in additional parameters to the cursor declaration to filter down which ones you want to add the user to.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 10, 2010 at 11:44 am

  #1101537

  would it be easier to create a role in the production database that has execute access to the 50 procedures, and then use a variation of Seth's script to add each user to the appropriate role?

  then when you copy the database to UAT, you just add the users to that role after the restoration and they can test?

  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!

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 10, 2010 at 1:11 pm

  #1101562

  Please don't cross post. It just wastes peoples time and fragments replies. Especially when you post one 6 hours after the other. 6 hours is not long to wait, especially on a weekend.

  No replies more to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic845004-359-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass