InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 10:54:10 GMT20http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxFirst, stop trying to use the EXECUTE AS [i]statement[/i]. Instead you should be using the EXECUTE AS [i]clause[/i] of the CREATE TRIGGER statement.Secondly, the login that you are using to create the trigger needs to have enough rights to do this impersonation also (the "sa" login or the sysadmin role should be able to do it).Tue, 04 Sep 2012 12:44:02 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHi GuysSo Far,- I have given devtest permissions to INSERT into the dba_ddl_events table- For the DDLTrigger, I have added Execute AS to 'devtest'- Added View Server State Permissions to devtest from the serverAny ideas on what I have missed outthanksTue, 04 Sep 2012 02:31:25 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHiI have tried adding AS EXECUTE to the following trigger but get the following error messageCannot execute as the server principal because the principal "devtest" does not exist, this type of principal cannot be impersonated, or you do not have permission. This happens when I try to create a table.I'm not too sure what permission level devtest needs to be to execute the trigger. I gave it sysadmin and got the following error. CREATE TRIGGER [Audit_Server]ON ALL SERVER FOR CREATE_DATABASE , DROP_DATABASEAS BEGINEXECUTE AS LOGIN = 'devtest'SET NOCOUNT ON;DECLARE@EventData XML = EVENTDATA();DECLARE @ip VARCHAR(32) =(SELECT client_net_addressFROM sys.dm_exec_connectionsWHERE 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();ENDGOMon, 03 Sep 2012 14:37:49 GMTpardeep.bhara 4684http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxYou can also add a WITH EXECUTE AS .. clause to the Trigger so that it can run under its own permissions, instead of having to rely on the User's. that's how I usually handle this problem.Fri, 31 Aug 2012 10:36:25 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxI will give it a try thankyou both for your help:-)Fri, 31 Aug 2012 10:00:41 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]Lowell (8/31/2012)[/b][hr]in that case, everyone who has CREATE DATABASE permissions must [b]also have [/b]INSERT permissions on the logging table[/quote]...and permissions on sys.dm_exec_connections (VIEW SERVER STATE permission)Fri, 31 Aug 2012 09:58:41 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxin that case, everyone who has CREATE DATABASE permissions must [b]also have [/b]INSERT permissions on the logging tableINSERT adminlog.dbo.dba_ddl_eventsno 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.Fri, 31 Aug 2012 09:53:42 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxCan i just clarify, the trigger was only created to track when ever a database was created or dropped. See belowCREATE 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(); ENDGOFri, 31 Aug 2012 09:51:19 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]dbadude78 (8/31/2012)[/b][hr]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:-)[/quote]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:[code]ALTER TRIGGER [TR_DB_NO_DROPPING_OBJECTS]on DATABASEFOR 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[/code]Fri, 31 Aug 2012 09:38:32 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]dbadude78 (8/31/2012)[/b][hr]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?[/quote]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...Fri, 31 Aug 2012 09:37:10 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHiYes i did, I have disabled it and it works now. I cannot believe it was a simple thing as that.Fri, 31 Aug 2012 09:30:41 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxYes 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:-)Fri, 31 Aug 2012 09:28:47 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]dbadude78 (8/31/2012)[/b][hr]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 13The user does not have permission to perform this action"I can confirm the login has been created with the dbcreator rights.[/quote]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 sameFri, 31 Aug 2012 09:19:51 GMTHowardWhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]dbadude78 (8/31/2012)[/b][hr]Yes I did, not sure what to do[/quote]the error message:[quote]"Msg 297, Level 16, State 1, Procedure Audit_Server, Line 13The user does not have permission to perform this action"[/quote]i think you have a server scoped DDL trigger named [b]Procedure Audit_Server[/b] 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?:[code]select * from sys.server_triggers[/code]Fri, 31 Aug 2012 09:17:40 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxYes I did, not sure what to doFri, 31 Aug 2012 09:10:09 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxdid you create the matching USER [b]devtest [/b]in the database where the procedure was created?did you grant execute to the user devtest ont eh procedure?[code]CREATE USER devtest FOR LOGIN=devtestGRANT EXECUTE ON myProcedure TO devtest[/code]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)Fri, 31 Aug 2012 07:51:58 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHi Thanks for the replyNormal 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=OFFGOEXEC sys.sp_addsrvrolemember @loginame = N'dev', @rolename = N'dbcreator'GOI login with the user account using sql authentication, try creating a database and it failsTry if the following works for youthanks Fri, 31 Aug 2012 07:44:34 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspx[quote][b]dbadude78 (8/31/2012)[/b][hr]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 13The user does not have permission to perform this action"I can confirm the login has been created with the dbcreator rights.[/quote]got it;if a [b]normal [/b]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.[code]--the EXECUTE AS must be a user in the database...not a loginCREATE procedure pr_CallBoostedSecurityProcess WITH EXECUTE AS 'superman'ASBEGIN'do priviledges stuff--dbcc freeproccacheCREATE DATABASE MyDatabaseEND [/code]Fri, 31 Aug 2012 07:21:33 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHi 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 13The user does not have permission to perform this action"I can confirm the login has been created with the dbcreator rights.Fri, 31 Aug 2012 06:53:19 GMTdbadude78http://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxnot 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.[code]CREATE LOGIN [devtest] WITH PASSWORD=N'NotTheRealPassword' , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ONGOEXEC master..sp_addsrvrolemember @loginame = N'devtest', @rolename = N'dbcreator'GOEXECUTE AS LOGIN='devtest'select suser_name() ;--Am i DevTest? yes I AmCREATE DATABASE myTEST;REVERT; --change back into supermanDROP DATABASE myTEST;DROP LOGIN devtest;[/code]Fri, 31 Aug 2012 06:09:18 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1352711-391-1.aspxHi GuysI 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 ideasThanksFri, 31 Aug 2012 05:15:27 GMTdbadude78