Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ajspruit911
Starting Member
3 Posts |
 Posted - 2009-06-11 : 05:21:45
|
| Hi AllIm really hoping you can help, im quite new to SQL and am having some issues with data access. I've read about 30 articles on SQL database roles and access and still not getting anywhere.Current set up:Created 2 SQL logins SQLDBO and SQLSA Added these 2 users to windows admin users so i can simply add and remove windows users to the relevent groups. Using Windows Auth for developers to connect to DB's via SQLDBO group. Both logins have public and sysadmin roles attached.I then added securables to the SQLDBO group to deny "create any database"However this doesn't stop them being able to create databases. So my Q.... what is the best way to go about granting the correct access for my developers? I want them to have full access to all databases on the server but i do not want them to be able to create databases.Any help would be much appreciated.ThanksAJ |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-11 : 10:23:34
|
| Permissions do not really apply to sysadmins. To give the login access to a particular database, you need to create the login, and do not add them to any server roles. Create a user mapped to that login in the database(s) you want the user to connect to, then give permissions within the database (membership in the db_owner role appears to be what you are looking for). This will give them full access to create tables, procedures, views, etc.. Even backup the database, but not restore the database (that requires server level permissions). |
 |
|
|
ajspruit911
Starting Member
3 Posts |
Posted - 2009-06-12 : 06:53:42
|
Thanks for the input mcrowley  , this is the way i had it set up originally but with over 250 Db's on this instance this takes alot of setting up and maintaining. Also with around 50 restores a week (from external source) - this action drops the user mapping when restoring so they require re-setting after the restores :( I have been working on an automated script to add the mapping automatically but this is still in the development stage and isn't going so well.any other ideas are much appreciated thanks again AJ |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-06-16 : 10:52:09
|
| If these are SQL Server logins, you can recreate them with the SID from the source system. You can find the SID that the database user has by checking sp_helpuser after a restore. Drop and recreate the login with that SID, and it should match up nicely for every restore afterward. With 250 databases, this could involve a lot of upfront work, but it will pay off in the long run.Alternatively, after the restore, you can run sp_change_users_login for each login in the database. This will update the SIDs the other way (user trued to login) |
|
|
|
|
|
|
|
|
SQL2005 Access