krishna_harik
asked on
how to grant permission to db_owner to restore the backup database file in sql server
Hi All,
my requirement is to grant permission to a db_owner (database role) to restore a database.
I know that it is possible by granting him sysadmin and dbcreator fixed server roles and
the owner (dbo) of the database.I tried with giving dbcreator but still he is not able to
restore a database what is reason ? and let me know how to retrict him not to create
new database and delete the other existing databases.
please, let me know is there any other way to achieve this task.
my requirement is to grant permission to a db_owner (database role) to restore a database.
I know that it is possible by granting him sysadmin and dbcreator fixed server roles and
the owner (dbo) of the database.I tried with giving dbcreator but still he is not able to
restore a database what is reason ? and let me know how to retrict him not to create
new database and delete the other existing databases.
please, let me know is there any other way to achieve this task.
ASKER
hi jegan,
thanks for your response.
i have created a database 'Test' and created a login 'test_admin' with password = N'****'
default database as 'test'
created a user 'test_user' with 'db_owner' only on 'Test' database.
And you suggest to revoke connection for all other databases to this login. Even if i run your script on any other database other than 'Test' database. it raises error
use db_name
go
REVOKE CONNECT FROM ur_login_name
error: Cannot find the user 'test_admin', because it does not exist or you do not have permission.
thanks for your response.
i have created a database 'Test' and created a login 'test_admin' with password = N'****'
default database as 'test'
created a user 'test_user' with 'db_owner' only on 'Test' database.
And you suggest to revoke connection for all other databases to this login. Even if i run your script on any other database other than 'Test' database. it raises error
use db_name
go
REVOKE CONNECT FROM ur_login_name
error: Cannot find the user 'test_admin', because it does not exist or you do not have permission.
Ok.. Since you have created this user newly, you would obtain this error since this user doesn't have any rights to the other database..
Kindly login to your instance using test_admin and your Test database alone should be visible for you.
You wont see any other databases in SSMS.
Kindly confirm
Kindly login to your instance using test_admin and your Test database alone should be visible for you.
You wont see any other databases in SSMS.
Kindly confirm
ASKER
correct, we can access only Test database.
i have given the db_creator server role to the Test_admin login and db_owner as database role.
when i am trying to restore database it is not allowing me to view the path of the drive location (SSMS GUI). Is this possible and we need to stop him creating new database and delete the existing databases.
is this possible
i have given the db_creator server role to the Test_admin login and db_owner as database role.
when i am trying to restore database it is not allowing me to view the path of the drive location (SSMS GUI). Is this possible and we need to stop him creating new database and delete the existing databases.
is this possible
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for sharing your inputs and ideas.. redesign, creating a instance may be helpfull.
1. Go to Security --> Logins
2. Right Click that login and choose Properties.
3. Go to User Mapping
4. Grant db_owner role for your database alone.
And issue this command from SSMS for all other databases:
use db_name
go
REVOKE CONNECT FROM ur_login_name
This should satisfy your requirement.