Grant dbcreator only for databases matching prefixExecuting 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
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;
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
asked 8 hours ago
metatursometaturso
1234 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
asked 8 hours ago
metatursometaturso
1234 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
asked 8 hours ago
metatursometaturso
1234 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.
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
sql-server t-sql permissions
asked 8 hours ago
metatursometaturso
1234 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
1234 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.
edited 8 hours ago
metaturso
asked 8 hours ago
metatursometaturso
1234 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
1234 bronze badges
asked 8 hours ago
metatursometaturso
1234 bronze badges
1234 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.
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 |
add a comment |
1 Answer
1
active
oldest
votes
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
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 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.
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
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
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 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
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 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
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 bronze badges
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
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 bronze badges
edited 7 hours ago
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 bronze badges
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 bronze badges
answered 7 hours ago
Erik DarlingErik Darling
24.9k13 gold badges76 silver badges125 bronze badges
24.9k13 gold badges76 silver badges125 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 |
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
1
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
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
1
@TonyHinkle I see an "or" in there, so I'm gonna skip that part 😉
– Erik Darling
5 hours ago
@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
@ErikDarling I am not a developer. Curious is it the new way (using EVENTDATA) of writing triggers? Very elegant.
– SqlWorldWide
4 hours ago
1
1
@SqlWorldWide It's XML, so not exactly elegant 😁
– Erik Darling
4 hours ago
@SqlWorldWide It's XML, so not exactly elegant 😁
– Erik Darling
4 hours ago
add a comment |
metaturso is a new contributor. Be nice, and check out our Code of Conduct.
metaturso is a new contributor. Be nice, and check out our Code of Conduct.
metaturso is a new contributor. Be nice, and check out our Code of Conduct.
metaturso is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
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
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown