x

sys.database_principals = null, removal with T-SQL

I would like to delete logins to which are of a null field in the DB, i am clearing down all unused logins and want to use the join below for the choice in tables

 SELECT  DBUSER.NAME AS 'USER' --DB_NAME() as ''Database'', DBUser.NAME AS ''User'', DBRole.NAME as ''db_role'', s.[name] AS ''Login''
 FROM sys.database_principals DBUser
  JOIN sys.database_role_members DBM ON DBM.member_principal_id = DBUser.principal_id
  JOIN sys.database_principals DBRole ON DBRole.principal_id = DBM.role_principal_id
 LEFT JOIN sys.server_principals s on DBUser.sid = s.sid
 WHERE s.[name] IS NULL
 ORDER BY DBUSER.NAME ASC;
more ▼

asked Apr 12 at 09:49 AM in Default

avatar image

seanthomas2010
1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x5

asked: Apr 12 at 09:49 AM

Seen: 18 times

Last Updated: Apr 13 at 11:00 AM

Copyright 2018 Redgate Software. Privacy Policy