April 25, 2011 at 4:19 pm
Hey everyone,
I have a security question here that I could use some help with.
In our environment, we have an instance that has 40 databases. Each database has it own unique login. 1 of the databases, let call it CentralHub, contains data that is required by all of the other databases for computing taxes on orders.
Now, until this week, we have done the amazing job of giving everyone SysAdmin rights on each of the logins. I am removing those. WOOT!!!!! Win one for us! Anyway, I would like to setup a single user inside of CentralHub and then tie all of the Logins to that one User for that database as well as their production environment.
So basically, I would create a login for Client1 and Client2. Each of these Logins would get tied to a User in Client1DB and Client2DB. However, in CentralHub, I would like them to both use the same user as I am lazy and it is easier than trying to manage 40 users for the one DB.
Can this be done and if so, what is the TSQL code for doing it? My current code sample is below.
Thanks,
Fraggle
USE Client1DB
; CREATE ROLE [SweetCustomRole] Authorization dbo
; ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [SweetCustomRole]
; ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [SweetCustomRole]
; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO SweetCustomRole
; DENY ALTER TO SweetCustomRole
; DENY VIEW DEFINITION ON SCHEMA :: [SYS] TO SweetCustomRole
; CREATE USER [SweetCustomDatabaseUser] FOR LOGIN [usrClient1] WITH DEFAULT_SCHEMA=[dbo]
; EXEC SP_ADDROLEMEMBER N'SweetCustomRole' , N'SweetCustomDatabaseUser'
USE Client2DB
; CREATE ROLE [SweetCustomRole] Authorization dbo
; ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [SweetCustomRole]
; ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [SweetCustomRole]
; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO SweetCustomRole
; DENY ALTER TO SweetCustomRole
; DENY VIEW DEFINITION ON SCHEMA :: [SYS] TO SweetCustomRole
; CREATE USER [SweetCustomDatabaseUser] FOR LOGIN [usrClient2] WITH DEFAULT_SCHEMA=[dbo]
; EXEC SP_ADDROLEMEMBER N'SweetCustomRole' , N'SweetCustomDatabaseUser'
USE CentralHub
GO
CREATE ROLE [SweetCustomRole] Authorization dbo
; ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [SweetCustomRole]
; ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [SweetCustomRole]
; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE TO SweetCustomRole
; DENY ALTER TO SweetCustomRole
; DENY VIEW DEFINITION ON SCHEMA :: [SYS] TO SweetCustomRole
-- this is where I get lost as I know you can tie it to a single login from above [usrClient1] or [usrClient2]
-- but how can you tie it to both?
; CREATE USER [SweetCustomDatabaseUser] WITHOUT LOGIN
; EXEC SP_ADDROLEMEMBER N'SweetCustomRole' , N'SweetCustomDatabaseUser'
April 25, 2011 at 6:52 pm
Do you have the option of using Windows groups and Windows authentication? If so, this is trivial. Put both sets of users in a Windows group. Make that Windows group the shared user in the database.
K. Brian Kelley
@kbriankelley
April 25, 2011 at 7:19 pm
K. Brian Kelley (4/25/2011)
Do you have the option of using Windows groups and Windows authentication? If so, this is trivial. Put both sets of users in a Windows group. Make that Windows group the shared user in the database.
Sadly that is not an option.
Fraggle.
April 26, 2011 at 9:35 am
Congrats on getting people out of the sysadmin role! That's a huge win!
Unfortunately (for your situation) a login can only be associated with one and only one Database User in any one particular database. Database Roles are at your service. Consider this:
- create Database Users in CentralHub for all the necessary logins
- create a new Database Role in CentralHub and add all new Database Users to it
- grant the necessary perms to the new Database Role
Yes, you'll have 40 Database Users in CentralHub, but in reality you'll only really be managing the Role in terms of permissions.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 9:59 am
Yea, I already had the role setup and it does make it easier. Just wish I didn't have to create users each time. :p Just me being lazy.
Fraggle
April 26, 2011 at 10:04 am
:p Just me being lazy.
That's one a my biggest strengths and biggest weaknesses 😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply