August 31, 2012 at 5:15 am
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
August 31, 2012 at 6:09 am
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
August 31, 2012 at 6:53 am
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.
August 31, 2012 at 7:21 am
dbadude78 (8/31/2012)
HiThanks 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
August 31, 2012 at 7:44 am
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
August 31, 2012 at 7:51 am
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
August 31, 2012 at 9:10 am
Yes I did, not sure what to do
August 31, 2012 at 9:17 am
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
August 31, 2012 at 9:19 am
dbadude78 (8/31/2012)
HiThanks 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
August 31, 2012 at 9:28 am
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:-)
August 31, 2012 at 9:30 am
Hi
Yes i did, I have disabled it and it works now. I cannot believe it was a simple thing as that.
August 31, 2012 at 9:37 am
dbadude78 (8/31/2012)
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?
Not sure what you're asking? You can obviously change the trigger definition so it just logs the event (to a table/email/wherever) and doesn't prevent it.
I don't see the point of checking whether someone has permission to create a database and only prevent it if they don't, SQL Server will take care of that for you...
August 31, 2012 at 9:38 am
dbadude78 (8/31/2012)
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:-)
well, the only people who can create new databases are those in the sysadmin role, or in dbcreator;
the trigger was created to prevent new databases, so you need to establish the rules, and test accordingly;
for example, I've made DDL triggers where only specific logins, and only from specific hostname(machines) are allowed to perform certain operations...
so only "BobTheSupervisor", myself, or "sa" are allowed, and even then, they must run the command from a specific hostname or IP Address.
, everyone else gets blocked.
something like this example is what i mean:
ALTER TRIGGER [TR_DB_NO_DROPPING_OBJECTS]
on DATABASE
FOR
DROP_PROCEDURE,DROP_FUNCTION,DROP_VIEW,DROP_TABLE
AS
BEGIN
--only two accounts allowed to drop stuff
IF suser_name() IN('sa','BobTheSupervisor','mydomain\lowell' )
BEGIN
--and only from two specific machines on the network
IF host_name() NOT IN('DEV223','PRODUCTION')
BEGIN
RAISERROR('Unauthorized use of drop object from inpermissible host.', 16, 1)
--prevent the drop
ROLLBACK
END
--ELSE --it was the right machine!
--BEGIN
--if it got to here, it was the "right" user from the "right" machine (i hope)
--END
END
ELSE
-- not the right login, Susie Ormand style [DENYED]
BEGIN
RAISERROR('Unauthorized use of drop object from inpermissible user.', 16, 1)
--prevent the drop
ROLLBACK
END
END --DB Trigger
Lowell
August 31, 2012 at 9:51 am
Can i just clarify, the trigger was only created to track when ever a database was created or dropped. See below
CREATE TRIGGER [Audit_Server]
ON ALL SERVER
FOR CREATE_DATABASE , DROP_DATABASE
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();
DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);
INSERT adminlog.dbo.dba_ddl_events
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO
August 31, 2012 at 9:53 am
in that case, everyone who has CREATE DATABASE permissions must also have INSERT permissions on the logging table
INSERT adminlog.dbo.dba_ddl_events
no insert permissions (line 13, right, like the error said?) would cause the rollback.
you could get around that using EXECUTE AS in my first example.
Lowell
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply