July 9, 2013 at 9:22 am
Hi All
We run both SQL 2008/2012 but this question stands for both versions.
I would like to delegate permissions to our helpdesk so that they can add new (SQL authenticated) user accounts to a particular DB then assign the db_datareader & db_datawriter roles to the account.
So, in Management Studio I have created a SQL login for the Helpdesk AD group and assigned the securityadmin server role, then gone to the relevant DB and assigned the db_securityadmin and db_access admin roles for this group.
Problem is that when the Helpdesk try to create a new user for this DB they can create a server login but get the following error when assigning DB roles to that user:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
I cancel out of the message only to find that the server login has been created for the new user but not the DB assignments.
The only way I have found to get around this is to assign the Helpdesk group to DBO DB role but this is just too permissive for them. Surely there is a way to do this without assign DBO for the Helpdesk??
Thanks in advance
July 9, 2013 at 9:47 am
how about creating a procedure that runs under elevated privilesges, and give the helpdesk permissions to that?
here's just one example:
CREATE procedure pr_CallBoostedSecurityProcess( @name varchar(128), @password varchar(128) )
WITH EXECUTE AS 'superman'
--this is a sysadmin login and also database user
--(only database users can be used with EXECUTE AS clause in a proc),
--which has been disabled, but is used for elevated permissions.
AS
BEGIN
--CREATE a login, if required.
DECLARE @cmd varchar(max)
IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)
BEGIN
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ 'N'' DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create Our role with specific permissions, if required.
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'MyStandardPermissions')
BEGIN
SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create our user, if required
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)
BEGIN
SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'
PRINT @cmd
EXECUTE(@cmd)
--assign the role to our user
SELECT @cmd ='EXEC sp_addrolemember N''MyStandardPermissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC
Lowell
July 9, 2013 at 9:56 am
Thanks for the reply, I'm not too hot on T-SQL but will read through it and see if I can work it out 🙂
July 10, 2013 at 7:33 am
OK, I've tweaked the elevated perms SP script as follows:
USE [SM_Test]
GO
/****** Object: StoredProcedure [dbo].[pr_CallBoostedSecurityProcess] Script Date: 10/07/2013 14:27:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pr_CallBoostedSecurityProcess]( @name varchar(20), @password varchar(10) )
WITH EXECUTE AS 'Obewan'
--this is a sysadmin login and also database user
--(only database users can be used with EXECUTE AS clause in a proc),
--which has been disabled, but is used for elevated permissions.
AS
BEGIN
--CREATE a login, if required.
DECLARE @cmd varchar(max)
IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)
BEGIN
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ ' DEFAULT_DATABASE=[SM_Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create Our role with specific permissions, if required.
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'MyStandardPermissions')
BEGIN
SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create our user, if required
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)
BEGIN
SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'
PRINT @cmd
EXECUTE(@cmd)
--assign the role to our user
SELECT @cmd ='EXEC sp_addrolemember N''MyStandardPermissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC
I've created the SQL login 'obewan' and added it to the sysadmin server role, but when I run the SP I get the following error:
CREATE LOGIN [test1] WITH PASSWORD = 'test', DEFAULT_DATABASE=[SM_Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'MyStandardPermissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.
Obewan has also been granted explicit perms to run this SP but should not need it due to it being a sysadmin anyway??
Any ideas folks?
July 10, 2013 at 7:54 am
because your obiwan user is going to be creating logins and also executing AS., it needs to be a sysadmin(for CREATE LOGIN) and db owner in the database (for CREATE USER)
this is exactly how i would script my super user in this case, and i'm assuming the database name here is [Sandbox], where yours is most likely something else:
--create our super user
CREATE LOGIN [Obewan] WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON;
GO
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'Obewan', @rolename = N'sysadmin';
GO
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [Obewan] DISABLE;
GO
USE [SandBox];
GO
CREATE USER [Obewan] FOR LOGIN [Obewan];
GO
USE [SandBox];
GO
EXEC sp_addrolemember N'db_owner', N'Obewan';
GO
Lowell
July 10, 2013 at 8:05 am
Apologies, I forgot to add that Obewan is part of the DB_owner role for the DB, and is a sysadmin as server level.
So do you need to grant explicit perms for CREATE USER , etc or should the role membership be sufficient? (just trying to better my understanding)
I'll give the new script a go thanks - very much appreciated 😎
July 10, 2013 at 9:01 am
Have created the superuser but get the following error now :angry:
CREATE LOGIN [test1] WITH PASSWORD = 'password',N' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'VFF_Permissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.
The above refers to the below and I can correct the syntax error if I remove the 'N', but then still get the permissions error I was getting previously. It shouldn't be this difficult!
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = ''' + @password +''','
+ 'N'' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
July 10, 2013 at 9:17 am
lloks like the string is constructed slightly wrong: thank goodness for the print statements!
CREATE LOGIN [test1] WITH PASSWORD = 'password',N' DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
i think the cod eneeds to be changed like this:
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = N'''
+ @password
+ ''',DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
Lowell
July 10, 2013 at 9:43 am
Cool, that's fixed the syntax error but not the permissions error:
CREATE LOGIN [test1] WITH PASSWORD = N'wibble',DEFAULT_DATABASE=[Master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'VFF_Permissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.
This is despite creating the sysadmin superuser using the previous script, do I need explicit grant permission on the SP itself?
July 10, 2013 at 10:38 am
As a further update to this, I can enable the sysadmin account then logon and create server logins/db users manually, but not via the stored procedure it would seem.
I've also granted explicit execute permissions to the SP but am sure that's an uncessary step, but worthy doing as a troubleshooting step.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply