Policy Based Mnagament - Check for orphaned users

Question

Policy Based Mnagament - Check for orphaned users

DBANewbie

Hall of Fame

Points: 3091
More actions
September 12, 2011 at 5:18 am

#247781

Hi,
Can anyone advise how set up a database policy which checks to see if i have any database users which are orphaned?
I'm really struggling.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Use an expression something like this ExecuteSQL('Numeric', select count (*) from ......................) > 0 and create a condition and a policy based on this condition.

select count (*) from ...................... query should give you the count of the orphaned logins

Thank You,

Best Regards,

SQLBuddy

DBANewbie Hall of Fame Points: 3091 More actions · Answer 2

Thanks for the advice, i'll give this ago when i'm back in the office and let you know how i get on.

Arjun SreeVastsva SSCertifiable Points: 7137 More actions · Answer 3

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

DBANewbie Hall of Fame Points: 3091 More actions · Answer 4

I'm still struggling with this.

In order to create a condition i need to specify a facet but i'm not sure which facet i should be using.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Use the Following :

CREATE A CONDITION USING THE FOLLOWING:

FACET : DATABASE

EXPRESSION:

ExecuteSql('Numeric', 'SELECT COUNT(*) FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid

WHERE b.sid IS NULL

AND a.type In (''S'', ''U'', ''G'')

AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')')

CONDITION should be the above EXPRESSION = 0

CREATE A POLICY USING ABOVE CONDITION ..

Thank You,

Best Regards,

SQLBuddy.

DBANewbie Hall of Fame Points: 3091 More actions · Answer 6

Thanks very much for you help, this is now set up and working.

Just to flesh out your explanation a little; here are the steps i followed within SSMS.

1. Create a new condition; setting "Database" as the facet.

2. On the left hand side of the expression builder, click on the 3 dots to open up the advanced editor.

3. Paste the following code in the advanced editor window:

ExecuteSql('Numeric', 'SELECT COUNT(*) FROM sys.database_principals a LEFT OUTER JOIN sys.server_principals b ON a.sid = b.sid

WHERE b.sid IS NULL

AND a.type In (''S'', ''U'', ''G'')

AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')')

4. On the right hand side of the expression; enter 0 (the query is counting the number or orphaned users and i want to evaluate it to ensure it is zero).

4. Name the condition and click okay.

5. Create a new policy which evaluates the condition which was just set up.

Thanks again for your help.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 7

SQLBuddy123

One Orange Chip

Points: 28294

September 19, 2011 at 4:42 pm

#1383571

Very Nicely done..

Thank You,

Best Regards,

SQLBuddy

SirOn Newbie Points: 7 More actions · Answer 8

I found another solution.... this works better for me.

--Orphaned Users Count Condition

--Facet: Server

--Expression:

ExecuteSql('Numeric', '

CREATE TABLE #orphanedusers(

InstanceName nvarchar(50) default @@SERVERNAME,

DatabaseName varchar(200) default db_name(),

UserName nvarchar(50),

UserSID binary(16)

)

DECLARE @command VARCHAR(1000)

SELECT @command = ''

USE [?]

INSERT INTO #orphanedusers (UserName,UserSID) EXEC sp_change_users_login Report

''

EXEC sp_MSforeachdb @command

select count(*) as OrphanedUsersCount from #orphanedusers

DROP TABLE #orphanedusers

')

--Operator: =

--Value: 0

http://www.croix.at/blog/sql-databases-list-all-orphaned-users-with-tsql-and-pbm/[/url]