db_creator permission not working on SQL Server 2008 r2

• HowardW

  One Orange Chip

  Points: 29109

  More actions

  August 31, 2012 at 9:58 am

  #1532503

  Lowell (8/31/2012)

  ---

  in that case, everyone who has CREATE DATABASE permissions must also have INSERT permissions on the logging table

  ...and permissions on sys.dm_exec_connections (VIEW SERVER STATE permission)

• dbadude78

  Ten Centuries

  Points: 1243

  More actions

  August 31, 2012 at 10:00 am

  #1532506

  I will give it a try thankyou both for your help

  🙂

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 31, 2012 at 10:36 am

  #1532521

  You 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.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• pardeep.bhara 4684

  Grasshopper

  Points: 10

  More actions

  September 3, 2012 at 2:37 pm

  #1533060

  Hi

  I have tried adding AS EXECUTE to the following trigger but get the following error message

  Cannot 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_DATABASE

  AS

  BEGIN

  EXECUTE AS LOGIN = 'devtest'

  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

• dbadude78

  Ten Centuries

  Points: 1243

  More actions

  September 4, 2012 at 2:31 am

  #1533151

  Hi Guys

  So 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 server

  Any ideas on what I have missed out

  thanks

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  September 4, 2012 at 12:44 pm

  #1533464

  First, stop trying to use the EXECUTE AS statement. Instead you should be using the EXECUTE AS clause 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).

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]