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 |
|
ITTrucker
Yak Posting Veteran
64 Posts |
 Posted - 2013-08-26 : 10:05:48
|
| Is there a clean way to drop a user from multiple databases at once?Our accounting software has 1 database for every company, and I'm trying out different settings to grant access to some reports without granting access to update the back end. (Apparently db_datareader is too restrictive for our reporting package) I have 14 databases that I need to open up and drop them from each time I try and test. I could just add every database and do a long script with every database but I was hoping there might be a cleaner way.USE [DB1]GOIF EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = N'USERNAME')DROP USER [USERNAME]GOUSE [DB2]... |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-26 : 12:40:23
|
quote:
Originally posted by ITTrucker
Is there a clean way to drop a user from multiple databases at once?Our accounting software has 1 database for every company, and I'm trying out different settings to grant access to some reports without granting access to update the back end. (Apparently db_datareader is too restrictive for our reporting package) I have 14 databases that I need to open up and drop them from each time I try and test. I could just add every database and do a long script with every database but I was hoping there might be a cleaner way.USE [DB1]GOIF EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = N'USERNAME')DROP USER [USERNAME]GOUSE [DB2]...
None that I know of that will drop a user from muliple database. What you are doing - dropping from one database at a time - is the only way I know. |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
|
|
|
|
|
Drop user from multiple databases