Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Oct 1 '12 at 2:32

This question came from our site for professional and enthusiast programmers.

    
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 Answer 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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