October 1, 2008 at 4:45 am
Is there a way I can set permissions for some logins to be able to detach a database but not drop it?
I've had a good look around and at the moment it appears to me that SQL Server's security model sees detaching on the same level as dropping.
TIA
October 1, 2008 at 5:18 am
Create two Stored procedures; one to detach the db and other to attach db and grant the exec permission to perticular user.
Also restrict the Database Role to perticular user as per your reuquirement.
Hope this will help!!!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 1, 2008 at 5:22 am
Did try that but SQL Server had a moan about the login not having privs to run sp_detach_db. Will try again though 🙂
October 1, 2008 at 5:35 am
Creat Stored procedure like User_sp_detach_db and use sp_detach_db as dynamic SQL in this stored procdure and control the permission of user_sp_detach_db 😛
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
October 1, 2008 at 5:47 am
The issue was with DBCC DETACHDB which is contained in the sp_detach_db stored proc. I think the issue was you needed sa rights to run DBCC DETACHDB.
Server: Msg 2571, Level 14, State 1, Line 1
User 'test' does not have permission to run DBCC DETACHDB.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply