November 14, 2007 at 8:55 am
My firm just got audited. It was recommended that we switch from SQL server authentication to windows authentication for all (15) Sql 2000 servers.
Is there a easy way of doing this. Meaning do I have to manually key 1500 users across the 15 Sql servers? Is there a tool that can be purchase that will batch create all of the users and give them their necessary roles?
thanks
November 14, 2007 at 5:57 pm
Putting my audit hat (CISA) on...
On any of your SQL Servers do you have 3rd party applications (or even home grown applications) which connect via SQL authentication? If so, can you consolidate the databases onto a handful of servers? One of the things you didn't mention was whether or not this is the case. If it is, that "wonderful" audit recommendation will break you. That needs to be worked out before you make any changes.
Second, with respect to switching over to Windows authentication...
1) Use Windows groups, not individual user accounts. This will hopefully simplify the work you have to do. Also, the groups should already exist that break down people's rights, if this has been done correctly within Active Directory.
2) Since you're having to revisit security, request the time to re-architect the security. Explain that since you're having to redo the security, you'd like to follow the principle of least privilege (only give folks the rights they need to work), you'd like to aggregate permissions together through the use of Windows group, thereby making Active Directory the determining factor on SQL Server access (this is a good idea... getting to one source for security). Also, you're going to need time to get people switched over. If they are using DSNs that are on their local workstations which say use SQL Authentication, these all need to be changed over or... you break.
3) If you get the time, investigate the various roles that are needed in each database (if these don't already exist). Map the appropriate Windows groups to these roles. If the appropriate Windows groups don't exist, request they get created and populated accordingly.
4) Go one server at a time, if possible. The KISS principle here.
5) As you enable a user's access, disable their individual SQL Server login access. Don't remove it from groups and don't delete the login, just disable it. This gives you a rollback plan. After a week or so of running fine (or going through amonth/quarter/fiscal year end as necessary), then delete the SQL Server login.
Based on what I've stated above, you're not going to find an automated tool. If you have to do this fast, if the Windows logins match up to the SQL Server logins (for instance, SomeDomain\JDoe and the SQL Server Login is JDoe), you can script the sp_grantlogin statements by doing somethng like:
SELECT 'EXEC sp_grantlogin [SomeDomain\' + name + ']'
FROM syslogins
WHERE isntname = 0
AND name NOT IN ('sa', 'guest')
You can write a similar script for each database to create the user mapping using sp_grantdbaccess. Query against sysusers and you're going to want to make sure issqlrole and isapprole = 0.
SELECT 'EXEC sp_grantdbaccess [SomeDomain\' + name + '], [SomeDomain\' + name + ']'
FROM sysusers
WHERE issqlrole = 0
AND isapprole = 0
AND name NOT IN ('dbo', 'guest')
To grant role memberships, you can write scripts using sysmembers.
SELECT 'EXEC sp_addrolemember [' + su1.name + '], [SomeDomain\' + su2.name + ']'
FROM sysmembers sm
JOIN sysusers su1
ON sm.groupuid = su1.uid
JOIN sysusers su2
ON sm.memberuid = su2.uid
WHERE su2.issqlrole = 0
AND su2.isapprole = 0
AND su2.name NOT IN ('dbo', 'guest')
Permissions are a harder animal. Hopefully you've put all permissions against database roles. Otherwise, you'll need to look at dumping the results from sp_helprotect and scripting back the permission application.
And this goes without saying... before making any changes, take the appropriate backups and verify them.
K. Brian Kelley
@kbriankelley
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply