October 12, 2011 at 10:13 pm
Sorry misspelled in Subject- Privileges--
Greetings DBAs...!
I have a priority issue in my bucket. It is like revoke SysAdmin privileges for the accounts which are not supposed to have admin access, either windows or SQL accounts.
I have 3000 + SQL Instances and more than 30+ sql logins minimum on every instance.
If I look into each instance and every login it should apply 3000 X 30+ times.
It may take several days to finish the task. And here I have to search for the employee IDs who have left the firm. This is impossible in short time complete.
Can anyone help in pointing me to correct direction and closing this easily.
Is this possible to revoke permissions for accounts and remove (accounts which emp who deosnt exists) using TSQL, if so please help in fixing the same using SQL Script.
Appreciate your help.. Thanks much in advance.
Cheers,
- Win.
" Have a great day "
October 13, 2011 at 4:28 am
Prepare a list of disabled users and add it to this code.
USE master;
GO
DECLARE @DisabledUsers TABLE (
LoginName nvarchar(128)
)
INSERT INTO @DisabledUsers VALUES ('Tom')
INSERT INTO @DisabledUsers VALUES ('[MyDomain\Harry]')
INSERT INTO @DisabledUsers VALUES ('[MyDomain\July]')
INSERT INTO @DisabledUsers VALUES ('Frank')
DECLARE @sql nvarchar(max)
SET @sql = (
SELECT N'EXEC master..sp_dropsrvrolemember @loginame = N'''+ QUOTENAME(logins.name) +''', @rolename = N''sysadmin''; ' AS [text()]
FROM sys.server_principals AS logins
INNER JOIN sys.server_role_members AS members
ON members.member_principal_id = logins.principal_id
INNER JOIN sys.server_principals AS roles
ON members.role_principal_id = roles.principal_id
WHERE roles.name = 'sysadmin'
AND logins.name IN (
SELECT LoginName
FROM @DisabledUsers
)
ORDER BY logins.name
FOR XML PATH('')
)
PRINT @sql
EXEC(@sql)
You can run against multiple servers creating a registered servers group in SSMS and selecting "New query" from the group's context menu.
Hope this helps
Gianluca
-- Gianluca Sartori
October 17, 2011 at 11:38 pm
Gianluca,
Thanks much and appreciate for your code. I will try to work with this on DEV environment and get back to you.
Sorry for the delayed response. was not in office.
- Win
Cheers,
- Win.
" Have a great day "
October 18, 2011 at 12:31 am
Hi,
I am testing the sql on 2000, it failed, since XML doesnt support on 2000. Iam good here.. I would like to have the script for SQL 2000 / 2005 / 2008 as well.
Tested on SQL 2005:
Solved the error.
It is generating the script to revoke permission. It is good one for me.
Cheers,
- Win.
" Have a great day "
October 18, 2011 at 2:09 am
Ouch! I didn't think it had to run on SQL 2000. Sorry about that.
If you remove the FOR XML PATH thing you can generate the scripts and run manually.
Good luck!
-- Gianluca Sartori
October 18, 2011 at 11:50 pm
Hello Gianluca,
Greetings...!!
It worked for me, thanks much.
One request to add to this : I forgot to verify users on DB. I checked for users who have created an object in DB, like Table, Procedures, etc. I was trying to pull data for the user defined objects, is there anyway to get details of Logins & Users who associated with in database level also along with instance level.
Is there any other way to clean such logins and users who left the firm and logins which are not supposed to be sysadmin? Can you / anyone point me to check the points to clean such accounts?
Correct me if Iam into wrong path.
- Win.
Cheers,
- Win.
" Have a great day "
October 20, 2011 at 5:25 am
Could anyone help me on this ??
Cheers,
- Win.
" Have a great day "
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply