September 4, 2009 at 10:43 am
Hello,
I have a couple issues I'm trying to resolve.
1.) I have a user that need to be able add user's and change passwords for user's I don't want to grant any other writes other then being able to create user's and change passwords. It would be nice if I could control it to only a defined set for DB's that he can create users for and change passwords for.
2) I need to grant rights for a couple users to either run specific SQL jobs. I don't want to grant any other rights other then being able to monitor the specific job and or run that job.
Any help would be greatly appreciated
September 4, 2009 at 11:29 am
I think this is exactly why they introduced the new EXECUTE AS command;
you don't REALLY want to give them extra rights, but they need to perform certain functions.
for #1, you could create a procedure , to CREATE LOGIN 'Whatever' and CREATE USER 'Whatever' FOR LOGIN 'Whatever' ; stick a copy of it in each database your semi-admin has access to, and make the procedure have EXECUTE AS in it.
similarly, for the specific SQL jobs, create a proc toy call that job, and include EXECUTE AS;
then you only need to give your normal users EXECUTE rights to the two procs you created, wiihtout overextending their normal rights.
Lowell
September 4, 2009 at 11:31 am
here's a similar example i did a while back; i was creating a database, and then adding roles, and then adding users, all as EXECUTE AS 'dbo'
you could picksome of the functions out of it to use as a model.
--i had to do this to my server:
--ALTER DATABASE master SET TRUSTWORTHY ON;
CREATE procedure MakeMeADatabase(@dbname varchar(128),@UserName varchar(128),@WithDevPriviledges int = 0)
WITH EXECUTE AS 'dbo'
AS
DECLARE @SQLCmd varchar(max)
BEGIN
IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)
BEGIN
PRINT 'Database Already Exists,No Need To Create.';
END
ELSE
BEGIN
PRINT 'Creating Database.';
set @SQLCmd = 'CREATE DATABASE ' + @DBName + ';';
exec (@SQLCmd);
END
SET @SQLCmd='
USE Whatever
--create the Role for my Dev guys
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverAdmins'' AND type = ''R'')
BEGIN
CREATE ROLE [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverAdmins]
END
--create role for my normal users
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverUsers'' AND type = ''R'')
BEGIN
CREATE ROLE [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverUsers]
END
--create the user if it does not exist yet
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''MyDBUser'')
CREATE USER [MyDBUser] FOR LOGIN [MyDBUser]
--add the user to the role
EXEC sp_addrolemember N''WhateverUsers'', N''MyDBUser''';
If @WithDevPriviledges 0
BEGIN
SET @SQLCmd= @SQLCmd + '
EXEC sp_addrolemember N''WhateverAdmins'', N''MyDBUser''';
END
SET @SQLCmd=Replace(@SQLCmd,'Whatever',@DBName);
SET @SQLCmd=Replace(@SQLCmd,'MyDBUser',@UserName);
PRINT @SQLCmd;
EXEC (@SQLCmd); ---two roles should be in place now
END
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply