Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

db_creator permission not working on SQL Server 2008 r2 Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 5:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 34, Visits: 582
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
Post #1352711
Posted Friday, August 31, 2012 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,895, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352744
Posted Friday, August 31, 2012 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 34, Visits: 582
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.
Post #1352773
Posted Friday, August 31, 2012 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,895, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352793
Posted Friday, August 31, 2012 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 34, Visits: 582
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


Post #1352812
Posted Friday, August 31, 2012 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,895, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352817
Posted Friday, August 31, 2012 9:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 34, Visits: 582
Yes I did, not sure what to do
Post #1352892
Posted Friday, August 31, 2012 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:30 PM
Points: 12,895, Visits: 32,089
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352900
Posted Friday, August 31, 2012 9:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,191, Visits: 9,879
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
Post #1352902
Posted Friday, August 31, 2012 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 34, Visits: 582
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
Post #1352911
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse