March 30, 2009 at 3:06 pm
I am would like to create a procedure where in when a Developer exec the proc it has to assign Read/write permissions to the users, ofcourse the user name will be passed as parameter.
thanks
March 30, 2009 at 3:12 pm
Good for you. What's the question?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 31, 2009 at 7:07 am
Read/Write permissions to what?
Does the person executing the procedure have permissions to manage security on the database (dbo, db_securityadmin)? If not you need to look at managing this using a certifcate. Check out this link for how to do it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2009 at 9:59 am
nope thats the issue here. I would like to create those procs " WITH EXEC AS DBO" so that person authorised to exec the proc can assign permissions.
Read/Write permissions to any database to any user in the server. HEre @dbname and @username will be passed as parameters.
I am not sure how safe this is to do and how effectively i can achieve. does anyone did so?
March 31, 2009 at 10:12 am
I've done this in the past when developers / users required access to the system tables / procs which required system admin or some other server level privileges.
It worked for me and became part of my standard roll-out.
March 31, 2009 at 10:23 am
SA (3/31/2009)
I've done this in the past when developers / users required access to the system tables / procs which required system admin or some other server level privileges.It worked for me and became part of my standard roll-out.
Can you explain how? I think that's what Tara wants/needs to know.
I think the signed procedures are the way to go and the link I provided in the my first post provides a step by step process on how to do it. The beauty of it is that you don't have to worry about anyone using the account since it is a USER without a LOGIN and you can avoid the EXECUTE AS statement.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2009 at 12:22 pm
Open the below in query window 1 and select up to "execute until here". The rest of the code is for cleaning up these temp objects.
USE master
GO
CREATE LOGIN test WITH PASSWORD = 'test', CHECK_POLICY = OFF
GO
USE MSDB
GO
CREATE ROLE db_joboperator AUTHORIZATION dbo;
GO
CREATE PROCEDURE sp_startjob
@jobname VARCHAR(100)
WITH EXECUTE AS OWNER
AS
IF LEN(@jobname) > 0
BEGIN
EXEC msdb.dbo.sp_start_job @job_name = @jobname
END
GO
CREATE USER [test] FOR LOGIN test WITH DEFAULT_SCHEMA = dbo
GO
sp_addrolemember 'db_joboperator', 'test';
GO
GRANT EXECUTE ON [dbo].[sp_startjob] TO [db_joboperator]
GO
--Execute until here
DROP USER [test]
GO
DROP ROLE db_joboperator
GO
DROP PROCEDURE sp_startjob
GO
USE master
GO
DROP LOGIN test
GO
Open query window 2 and change the connection to sql user/pwd "test"/"test" and execute the code below. Change the job name to one that already exists on the server
use msdb
go
sp_startjob @jobname = 'exec_job'
Go
Jack,
I've not explored signed modules yet. However, I've used the above method extensively for providing production support access to Production jobs etc.
March 31, 2009 at 1:10 pm
SA (3/31/2009)
Jack,I've not explored signed modules yet. However, I've used the above method extensively for providing production support access to Production jobs etc.
I want you to know I wasn't being critical, just curious. I have seen the type of solution you are using before, but I think signed modules is probably a better solution when you are talking about granting permissions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 31, 2009 at 1:26 pm
Hey no worries...sometimes critics are you're best friends 😀
March 31, 2009 at 7:25 pm
guys i didnt follow what that script id doing, but Tara wants to allows a non sys admin to grant access to windows users for the particular databases where database and iserid will be selected by the user who exec the procedure.
Tara..let me know if am worng.
April 1, 2009 at 7:40 am
I don't have something that specifically allows a user to do that. However, I can see how that can be done using dynamic SQL.
You could alternately provide securityadmin (server role) and db_securityadmin (database role) to the user / group that needs this access.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply