Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minimum rights required to add users to a DB


Minimum rights required to add users to a DB

Author
Message
Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 39023
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

--
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!

Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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 :-)
Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 39023
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

--
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!

Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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 Cool
Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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;'

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 39023
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

--
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!

Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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?
Oldskool
Oldskool
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 29
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search