sp_detach_db under Windows Authentication
-
I am using Windows Authentication and I am trying to automate detaching old DBs, but I always run into the same error message "User 'ACCNT\test1' does not have permission to run DBCC DETACHDB"
Does the user have to belong to the sysadmin fixed server role only to be able to execute sp_detach_db? I have not been able to find that option in any other server role or database role.
I just don't want to give sysadmin rigths to the people in charge of detaching old DBs.
Any ideas?
Carlos
-
i'm guessing here but i bet you have to be a memeber of diskadmin as a minimum?
MVDBA
-
A bit of dead-end research:
BOL says that sp_detach_db can only be run by SysAdmins. That's usually good enough for me, but every now and then you need to know what's really going on inside SQL Server. Next step: look at the source code for sp-detach_db. In there you soon find that it calls DBCC DETACHDB. There is no BOL entry for this DBCC, making it <dramatic chord> an undocumented command!
As the people who wrote these (Microsoft) aren't saying what they are or what they do--at least in a forum that makes them liable if they're wrong or their code is buggy--all bets are off. A quick Google check doesn't turn up anything particularly useful on this.
Last checks (since I'm waiting for a looong FTP download): I configured a test login, gave it various combinations of dbo and the server roles except SysAdmin, and in Query Analyzer issued both sp_detach_db and DBCC DETACHDB. Each and every time, I got back:
Server: Msg 2571, Level 14, State 1, Line 1
User 'guest' does not have permission to run DBCC detachdb.
Safe conclusion: only SysAdmins can detach databases.
Philip
-
Thanks Philip,
I did the same test with same results !!!. I already posted the question on the MSDN group. Awaiting for reply.
Thanks for your time !!!
Carlos
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply