SQL Server Performance Forum – Threads Archive
Centralized User Logins and Permisions
I’m a new DBA and I would like some insight on creating a centralized user repository. What I have is 15 SQL2k servers with a total of 50 databases and 600+ users. I would like to be able to manage user logins and permissions to there respective databases from one server and/or database. (example: Joe User, Server and Database access to: SQL1/DB2, SQL5/DB8, SQL15/DB1) Is this even possible? Thank you in advanceDenis
Open SQL Server Enterprise manager and drill down to Server->Security->Logins. Right click on the login in question, select ‘Properties’. In the properties window, select the Database Access tab. This should give you what you need.
Thank you for your response. That’s not quite what I was looking for. What I would like to do is have one physical server that I add users too. I am trying to get away from having to add them to each individual physical server. (ie. New User needs access to 8 different physical servers. I will have to add that same user 8 times.) I would like to only do it once. Thank you
Denis
Hi Denis, If you have:
windows groups defined as SQL logins
SQL logins have corresponding users in the databases
roles within the database
roles assigned to the users
permissions assigned to the roles then you can add your users to the windows groups using Windows NT tools There are no ‘out of the box’ utilities provided with SQL Server (although there could be third party products…) Cheers
Twan
‘Proxy’ database functionality is not included within the SQL Server security model. Try
1. Consolidating your server and databases therein based on current user access patterns.
2. Using groups instead of users to manage permissions – if you are using integrated security within an Active directory domain this should be easy.
3. Develop/download/buy a utility which will allow you to centrally manage security for all your server/databases. I don’t know of any right now. Nathan H.O.
Moderator
SQL-Server-Performance.com
Thank you all. Now I have some things look at. Denis
]]>