Cannot attach or detach databases in SQL Server Enterprise Manager
-
I have users who have a need to attach and detach db's on their db server. However, I want them to have the minimum permissions necessary to do this. We're running SQL Server 2000, sp4 with hybrid security on the server and the users have access to Enterprise Manager on their desktops. The users belong to a Windows group called "RiskMgmt" and
\RiskMgmt is a logon on the SQL Server. I have assigned the following roles to the RiskMgmt SQL logon: - Database Creators
- Disk Administrators
- Setup Administrators
- Process Administrators
- Bulk Insert Administrators
When the users receive a new MDF which they wish to attach, they use Enterprise Manager (EM). However, for them, the "Attach database..." option is grayed out in EM. If I logon to the user's desktop and run their EM, I do see the "Attach" option in EM. So this would seem to be a permissions problem.
However...
When the user is logged on to their desktop and run sp_attach_single_file_db in Query Analyzer they get the error message, "
\ is not a valid username in " -- HOWEVER, the db *does* get attached. Looking back in EM, the user still doesn't have the attach or detach options available. According to SQL Server Books Online, "... members of the sysadmin and dbcreator fixed server roles can execute ..." sp_attach_db. The way I read this, you don't need to be a sysadmin to attach / detach databases - being a dbcreator should be good enough. But it isn't.
I think there's another permissions issue here that I'm not familiar with. Can someone help? TIA very much.
Tony
-
being a dbcreator should be good enough. But it isn't.
Yes it is. BOL doesn't say anything about dbcreater being able to use EM to attach/detach databases. It says dbcreater gives you permission to use sp_attach_db, which you users can and have run (according to your post).
I think the problem is that they are attaching a database (which means it already existed somewhere) and their login is not a user for that database. The issue is that once they attach the database, SQL Server is trying to make them the DBO. However, it can't do that since they are not a user of the database.
-SQLBill
-
Thanks SQLBill - you're exactly right with everything that you said. BOL does _not_ say that dbcreator will show the attach option in EM. I assumed that it would, though, since dbcreator allows you to run sp_attach_db. Yeah, yeah - I remember what they say about "assuming."
Also, you're exactly right about what the problem is - they cannot become DBO because their user account does not exist in the DB (which came from a different office). But I don't understand why they cannot... dbcreator does not allow this to happen? Is there a way to make this happen without granting the sysadmin role?
Thanks very much for your quick reply.
Tony
-
They have to be added as a user somehow. How do you add them as a user now? Do you do it yourself?
They either need to be sysadmin, have their login added to the database by the other office, or accept that the error will happen but that the database is still attached.
-SQLBill
-
Hmmmm... bummer. Thanks for your reply, though.
To answer your question, I do just add their user account (actually Windows group account) to the DB after it's attached. These guys probably wouldn't be comfortable with executing scripts - even just sp_attach_db - which gives you some idea of the level they're at and why I'm hesitant to give them the sysadmin role.
One more question: any idea what specific permission the sysadmin role has which allows one to use EM to attach DB's? With respect to EM, it doesn't matter if the user has a defined acct in the DB or not - EM just won't allow a non-sysadmin to attach DB's. In other words, even if I asked the other office to add a SQL account in their DB's that we could use before they send them to us, my users still wouldn't be able to attach it via EM without the sysadmin role. But if ther'es a specific permission I could add to their logon to allow them to attach via EM... well that would be good enough and I could then take away the sysadmin role.
Thanks again for all your help!
Tony
-
Got a possible solution......
Try creating a stored procedure. Call it something like dbo.usp_addnewdb.
Make it a two step procedure.
1. the procedure runs sp_attach_db
2. the procedure then adds the role to the database.
You should create the stored procedure with one variable - the name of the database to be attached.
The first part of the procedure would take the first variable/parameter and add that to the 'inner' stored procedure of sp_attach_db.
And you could 'hard code' the role into the procedure.
The procedure would have to have all commands start with dbo (ie. dbo.sp_attach_db).
-SQLBill
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply