Is this possible?
I would like to have a limit for a user, lets say 5 databases.
So when he tries to issue a CREATE query to create a 6th an exception is thrown.
+3
A:
No, you cannot do this - at least not in a declarative way (just simply specify the max number of databases owned for each user).
The closest you could get with standard SQL Server functionality would be to create a DDL trigger for CREATE DATABASE and in that trigger, check to see if the current user already owns five databases, and in that case, let the trigger fail the operation.
Something along the lines of this (taken from TechNet sample):
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
-- here, check to see if current user already owns five databases
-- and if so, fail the trigger by using RAISERROR
GO
marc_s
2010-09-01 21:49:42
What about if/when they run sp_changeDBOwner?
Philip Kelley
2010-09-01 21:55:55
@Philip Kelley: well, in that case, you might have to trap yet some more DDL operations.... as I said - it could get pretty messy, and there's no easy, "out-of-the-box", declarative way to do this...
marc_s
2010-09-01 21:57:28
+1
A:
look into DDL triggers, with a trigger like this one below you can trap the CREATE DATABASE statement
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
--do something here
-- select count(*)from sys.sysdatabases where sid = ???
GO
SQLMenace
2010-09-01 21:53:14