April 19, 2012 at 12:37 pm
We have a vendor requesting access to a SQL instance in our test environment. In short, they will need to create and delete the same database several times, and have full control over it when it exists. I do not want to grant sysadmin rights to the account, but I am OK with granting other server-level roles such as dbcreator. Is there any way to have the vendor's account automatically added as a database user when they create the database?
April 19, 2012 at 1:24 pm
I just tested it out...you can add the user to model and put it in the db_owner Role and anytime a new DB is created the user will be there. Just know that this approach will give them rights to model itself as db_owner.
You could also add them to the dbcreator Fixed Server Role. When they create a DB they will be made the owner of that DB, so will be able to to anything with it. I like to have all my DBs owned by sa, but in your case it may be OK to have them own DBs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 2:10 pm
You might be able to put something in a DDL trigger on the Create_Database event. I haven't tried that, but it sounds like it should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2012 at 3:48 pm
Thanks for the responses guys.
I originally tried adding the account to just dbcreator, but it seems that this does not actually make the account "owner" in this case. I already blew up my test database, but I'll go take another look at this.
The model db suggestion could be a decent workaround if nothing else. I will definately keep this in mind.
I had also tried the create_database trigger but I could only get the account created in master, not the new database. Apparently, you can't use a "USE" statement in a trigger (I'm not much of a coder). Is there anything else I can try with the trigger specifically to make this happen?
April 19, 2012 at 3:55 pm
qgudex (4/19/2012)
Thanks for the responses guys.I originally tried adding the account to just dbcreator, but it seems that this does not actually make the account "owner" in this case. I already blew up my test database, but I'll go take another look at this.
Check it out again. Maybe you got crossed up in testing. I did test it before posting to make sure but maybe I got crossed up. As I know it the person who creates a database is automatically made to be the owner.
I had also tried the create_database trigger but I could only get the account created in master, not the new database. Apparently, you can't use a "USE" statement in a trigger (I'm not much of a coder). Is there anything else I can try with the trigger specifically to make this happen?
You can use dynamic SQL:
DECLARE @sql NVARCHAR(MAX);
DECLARE @dbname SYSNAME;
-- >> set @dbanme from event data here
SET @sql = 'USE ' + @dbname + '; CREATE USER blah blah blah';
EXEC (@sql);
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 23, 2012 at 5:30 am
Hi
Can the thread know.. whether the issue is resolved.
if solved then do let us know the steps.
Thank you
April 23, 2012 at 10:22 am
opc.three,
You were right. If an account with the dbcreator server role creates a database, they do become the owner and have full access. I'm not sure what I was doing last week, but this solution is sufficient for our test environment.
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply