Mfcttrf

How to unit test methods which using static methods?

What is an example of of idiomatic "typed" WolframScript?

Can SOCPs approximate better than LPs?

Comment traduire « That screams X »

What exactly did Ant-Man see that made him say that their plan worked?

Most elegant way to write a one-shot 'if'

Is Cyclic Ether oxidised by periodic acid

What is "oversubscription" in Networking?

What will happen if I checked in for another room in the same hotel, but not for the booked one?

How to properly say asset/assets in German

How is this practical and very old scene shot?

Different budgets within roommate group

Is there a legal way for US presidents to extend their terms beyond two terms of four years?

What game is this character in the Pixels movie from?

Security Patch SUPEE-11155 - Possible issues?

How do I organize members in a struct to waste the least space on alignment?

How do I tell the reader that my character is autistic in Fantasy?

Copy group of files (Filename*) to backup (Filename*.bak)

Reusable spacecraft: why still have fairings detach, instead of open/close?

Converting Geographic Coordinates into Lambert2008 coordinates

Why would anyone even use a Portkey?

Using the ArcGIS 'select by location' tool in ModelBuilder?

Sharing referee/AE report online to point out a grievous error in refereeing

Warnings of R. Chaim Vital

Grant dbcreator only for databases matching prefix

Executing sys.dm_fts_parser without sysadmin server roleGRANT EXECUTE ON PROCEDURE unable to USE databasestored procedure can select and update tables in other databases - minimal permissions grantedWildcard in CREATE grant in MySQLSQL Server: What securables to enable a server role for read-write access to all DBsMongoDB: Privileges on objects created by me only?Accessing Table Data ONLY From ViewMySQL: Grant Read-Only DB Access for one DB and Create/Drop Access for other DBsCan you DENY access to a certain schema for a user with dbcreator role?How to grant execute permissions to a stored procedure but not to the underlying databases

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?

sql-server t-sql permissions

share|improve this question

edited 8 hours ago

metaturso

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

4

In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?

sql-server t-sql permissions

share|improve this question

edited 8 hours ago

metaturso

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

In Microsoft SQLServer 2017+ I would like to grant the dbcreator role on a single user but only allow her to create databases whose name matches a fixed prefix.

Is it possible to do this at the database level using a built-in feature or a stored procedure?

sql-server t-sql permissions

share|improve this question

edited 8 hours ago

metaturso

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 8 hours ago

metaturso

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 8 hours ago

metaturso

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

metatursometaturso

12344 bronze badges

New contributor

metaturso is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 hours ago

metatursometaturso

12344 bronze badges

1 Answer
1

active

oldest

votes

7

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  But the question asked if it could be done with a stored procedure--so you need to have the trigger exec a stored procedure. :)
  
  – Tony Hinkle
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @TonyHinkle I see an "or" in there, so I'm gonna skip that part 😉
  
  – Erik Darling
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ErikDarling I am not a developer. Curious is it the new way (using EVENTDATA) of writing triggers? Very elegant.
  
  – SqlWorldWide
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SqlWorldWide It's XML, so not exactly elegant 😁
  
  – Erik Darling
  4 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

metaturso is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f241480%2fgrant-dbcreator-only-for-databases-matching-prefix%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

7

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  But the question asked if it could be done with a stored procedure--so you need to have the trigger exec a stored procedure. :)
  
  – Tony Hinkle
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @TonyHinkle I see an "or" in there, so I'm gonna skip that part 😉
  
  – Erik Darling
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ErikDarling I am not a developer. Curious is it the new way (using EVENTDATA) of writing triggers? Very elegant.
  
  – SqlWorldWide
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SqlWorldWide It's XML, so not exactly elegant 😁
  
  – Erik Darling
  4 hours ago
  

  
  
  
  

add a comment | 

7

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  But the question asked if it could be done with a stored procedure--so you need to have the trigger exec a stored procedure. :)
  
  – Tony Hinkle
  6 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @TonyHinkle I see an "or" in there, so I'm gonna skip that part 😉
  
  – Erik Darling
  5 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ErikDarling I am not a developer. Curious is it the new way (using EVENTDATA) of writing triggers? Very elegant.
  
  – SqlWorldWide
  4 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @SqlWorldWide It's XML, so not exactly elegant 😁
  
  – Erik Darling
  4 hours ago
  

  
  
  
  

add a comment | 

You can use a server level trigger like this:

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

Or at the database level like this:

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

CREATE OR ALTER TRIGGER [database_name_check] ON ALL SERVER
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(256)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO
ENABLE TRIGGER [database_name_check] ON ALL SERVER;
GO

USE StackOverflow2013
GO 

CREATE OR ALTER TRIGGER [database_name_check] ON DATABASE
 FOR CREATE_DATABASE
AS
 BEGIN
 SET NOCOUNT ON; 

 DECLARE @event_data XML; 
 SET @event_data = EVENTDATA();
 IF ((SELECT @event_data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(255)') ) = 'NADABRUTOedarl'
 AND (SELECT @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(255)') ) NOT LIKE '%Stack%')
 BEGIN
 RAISERROR('NO CAN DO, BUCKAROO', 0, 1) WITH NOWAIT;
 ROLLBACK;
 END

 END;

GO

ENABLE TRIGGER [database_name_check] ON DATABASE;
GO

share|improve this answer

edited 7 hours ago

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges

answered 7 hours ago

Erik DarlingErik Darling

24.9k1313 gold badges7676 silver badges125125 bronze badges