SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


db_creator permission not working on SQL Server 2008 r2


db_creator permission not working on SQL Server 2008 r2

Author
Message
dbadude78
dbadude78
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 707
Hi Guys

I have created an sql server login called devtest and have given it dbcreator permssions.
When I login with the user account and try to create a database I get the following error "User does not have permissions to perform the following action".

I really dont understand what is happening, I thought dbcreator would give access to create databases.

Any ideas

Thanks
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
not sure what the specific issue is; this works perfectly fine, whcih is just the scripting of what you said you did;
note i'm using EXECUTE AS to directly test the permissions.

CREATE LOGIN [devtest] WITH PASSWORD=N'NotTheRealPassword' , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'devtest', @rolename = N'dbcreator'
GO

EXECUTE AS LOGIN='devtest'
select suser_name() ;--Am i DevTest? yes I Am
CREATE DATABASE myTEST;
REVERT; --change back into superman
DROP DATABASE myTEST;
DROP LOGIN devtest;



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!
dbadude78
dbadude78
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 707
Hi

Thanks for the quick reply. As a test i executed the whole script on my local instance. But got a the following message "Msg 297, Level 16, State 1, Procedure Audit_Server, Line 13
The user does not have permission to perform this action"

I can confirm the login has been created with the dbcreator rights.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
dbadude78 (8/31/2012)
Hi

Thanks for the quick reply. As a test i executed the whole script on my local instance. But got a the following message "Msg 297, Level 16, State 1, Procedure Audit_Server, Line 13
The user does not have permission to perform this action"

I can confirm the login has been created with the dbcreator rights.



got it;
if a normal user calls that procedure, it would fail, because the normal user doesn't have permission to create database.

you would need to use EXECUTE AS on the procedure, if normal users are going to call it.


--the EXECUTE AS must be a user in the database...not a login
CREATE procedure pr_CallBoostedSecurityProcess
WITH EXECUTE AS 'superman'
AS
BEGIN
'do priviledges stuff
--dbcc freeproccache
CREATE DATABASE MyDatabase
END




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!
dbadude78
dbadude78
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 707
Hi

Thanks for the reply

Normal user 'dev' has got dbcreator rights to perform the appropriate actions. See below;

CREATE LOGIN [dev] WITH PASSWORD=N'1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

EXEC sys.sp_addsrvrolemember @loginame = N'dev', @rolename = N'dbcreator'
GO

I login with the user account using sql authentication, try creating a database and it fails

Try if the following works for you

thanks
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
did you create the matching USER devtest in the database where the procedure was created?

did you grant execute to the user devtest ont eh procedure?

CREATE USER devtest FOR LOGIN=devtest
GRANT EXECUTE ON myProcedure TO devtest



if you login and don't have a user mapped, the logincannot execute a stored proceudre...only users can.

a login does not have any rights to objects within a database. (unless it's int he sysadmin role...that shortcuts/trumps user permissions)

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!
dbadude78
dbadude78
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 707
Yes I did, not sure what to do
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73257 Visits: 40960
dbadude78 (8/31/2012)
Yes I did, not sure what to do


the error message:

"Msg 297, Level 16, State 1, Procedure Audit_Server, Line 13
The user does not have permission to perform this action"



i think you have a server scoped DDL trigger named Procedure Audit_Server that is designed to prevetn all new database creations...dunno how i missed that before.

do you see that procedure in the results of this query?:

select * from sys.server_triggers



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!
HowardW
HowardW
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6451 Visits: 9892
dbadude78 (8/31/2012)
Hi

Thanks for the quick reply. As a test i executed the whole script on my local instance. But got a the following message "Msg 297, Level 16, State 1, Procedure Audit_Server, Line 13
The user does not have permission to perform this action"

I can confirm the login has been created with the dbcreator rights.


You received that error when running Lowell's script, as it's posted? Have you got a DDL trigger on CREATE DATABASE that's manually preventing this?

Edit: Ha, Lowell's just spotted the same
dbadude78
dbadude78
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 707
Yes I have got a DDL trigger, I have disabled it and it works now. Thankyou for your help :-)
The reason i put the triggers was to monitor when a new database is created. Any ideas on how i can set the trigger so that the dbcreator works while its on?

thank you once again:-)
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