SQL Server Performance Forum – Threads Archive

Grant rights in a stored procedure

I have a script that restores a database and grants rights to the users. I was using sp_dropuser, sp_grantdbaccess, and sp_addrolemember for this reason.
USE MyDB
GO exec sp_dropuser ‘Userid’
exec sp_grantdbaccess ‘Userid’,’Userid’
exec sp_addrolemember ‘db_owner’,’Userid’ In order to automate this RESTORE, I created a SP and put my script in it but I can not use USE command in a SP and therefore cannot use the above sp_…s. What can I do? CanadaDBA

How about exec MyDB.dbo.sp_dropuser etc.?

It worked! Why I didn’t think about this way? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thank you, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan How about exec MyDB.dbo.sp_dropuser etc.? <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

Seems still there is a problem in somewhere else! In the SP, after the DB is restored and users are defined, I need to grant execute rights to a userid. Here is my code:
declare @objname varchar(100) declare namelist insensitive cursor for
select name
from [MyDB].[dbo].sysobjects
where xtype in(‘p’,’fn’) AND category=0 open namelist
fetch next from namelist into @objname
while @@fetch_status=0
begin
exec(‘grant execute on [dbo].[‘ + @objname + ‘] to [Userid]’)
fetch next from namelist into @objname
end close namelist
deallocate namelist
In exec(‘grant execute on [dbo].[‘… I get an error because the object does not exist in the current database. I tried to use it as exec(‘grant execute on [MyDB].[dbo].[‘ + @objname + ‘] to [Userid]’) but it didn’t work.
CanadaDBA

IF (SELECT OBJECT_ID(@objname)) IS NOT NULL
BEGIN
EXEC(‘grant …………’)
END … and if you need to qualify the object name with the database name, you could script it like this: EXEC (‘IF (SELECT OBJECT_ID(”MyDB.dbo.’ + @objname + ”’)) IS NOT NULL BEGIN GRANT EXECUTE ON MyDB.dbo.’ + @objname + ‘ TO [UserId] END’)

This is almost what I have in the above code. The problem is that MyDB. is not allowed in GRANT command. The DB must be in use. GRANT EXECUTE ON MyDB.dbo.’ + @objname + ‘ TO [UserId] CanadaDBA

You can put USE MyDB within the script you want to execute, as it is only valid within the script being executed. EXEC (‘ USE MyDB IF (SELECT OBJECT_ID(”’ + @objname + ”’)) IS NOT NULL BEGIN GRANT EXECUTE ON dbo.’ + @objname + ‘ TO [UserId] END ’) I created a test procedure, and it certainly works while connected to a different database than MyDB – provided you are already connected with security admin permissions: My first test actually had GO after the USE MyDB instruction, like you would do in a script, but it fails with an "Incorrect syntax near ‘GO’." error message. You can use a semi-colon (<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> instead of GO if you want to make the script a bit easier to read.

I really dont know you people are taking this much straing after restoring a database. I do restore, then use one script has EXEC sp_change_users_login ‘Report' and fixed using EXEC sp_change_users_login ‘update_one’, @username, @username or sp_change_users_login ‘autofix’, @username actually i compared my work iwth urs, now totaly scared wherther my works lacks somthing or wil creat any pbm in future <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) . tel me wht u ppl feel

Cool Man![8D] It solved my problem and saved my life!! Using USE as part of the string looks nice and tricky. Thanks, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan You can put USE MyDB within the script you want to execute, as it is only valid within the script being executed. EXEC (‘ USE MyDB IF (SELECT OBJECT_ID(”’ + @objname + ”’)) IS NOT NULL BEGIN GRANT EXECUTE ON dbo.’ + @objname + ‘ TO [UserId] END ’) I created a test procedure, and it certainly works while connected to a different database than MyDB – provided you are already connected with security admin permissions: My first test actually had GO after the USE MyDB instruction, like you would do in a script, but it fails with an "Incorrect syntax near ‘GO’." error message. You can use a semi-colon (<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> instead of GO if you want to make the script a bit easier to read. <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

I was not able to use this sp to fix my problem. The original problem is that I want to restore everynight’s production backup on my Dev server on the next day. "Userid" needs to have execute right on the database’s PSs and UDFs. <pre id="code"> EXEC master.dbo.isp_MyDB_Restore_Last_Night_Backup /* Restores the backup */ EXEC master.dbo.isp_MyDB_Grant_Rights </pre id="code"> inside the isp_MyDB_Grant_Rights I have a loop that lists the SPs and UDFs in MyDB and grants execute rights to them (I have posted the the code under this topic). The grant part is done by the following: <pre id="code"> exec(‘grant execute on [dbo].[‘ + @objname + ‘] to [Userid]’) </pre id="code"> The problem was that I was not allowed to use USE [database] in an stored procedure. And because the isp_MyDB_Grant_Rights was in master, the Userid was not known in master database. Thanks, <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by gkrishn I really dont know you people are taking this much straing after restoring a database. I do restore, then use one script has EXEC sp_change_users_login ‘Report' and fixed using EXEC sp_change_users_login ‘update_one’, @username, @username or sp_change_users_login ‘autofix’, @username actually i compared my work iwth urs, now totaly scared wherther my works lacks somthing or wil creat any pbm in future <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />) . tel me wht u ppl feel <hr height="1" noshade id="quote"></blockquote id="quote"> CanadaDBA

]]>