5

I want to create a user having db_create, db_restore server roles and will be db_owner of that database. But there is only 1 server role available, that is db_creator, which can create database and is also the db_owner of that. But when I restore another database backup that gets disconnected, i.e. not accessible.

In sql server is there any way that can create and restore db and will be dbowner of that database. I don't want to grant sysadmin server roles to that user.

Please help me out.

  • I haven't tested it, but GRANTing "ALTER ANY DATABASE" in addition to dbcreator might do the trick. – TToni Sep 6 '12 at 14:23
1

But when I restore another database backup that gets disconnected, i.e. not accessible.

You have to add the user to the db_owner role after the restore. You can use

ALTER ROLE [db_owner] ADD MEMBER [frank]

or EXEC sp_addrolemember 'db_owner', 'frank';

Note:

ALTER ANY DATABASE - You dont want to give the user too much rights since this will allow the user rights for all the databases.

Grants or denies the ability to drop or modify existing databases as well as create new databases. The dbcreator fixed server role has this permission granted implicitly.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.