July 14, 2016 at 5:40 am
how can i get the [ ] characters around the username? nested quotes is probably my least favorite thing right now. i'm trying to generate output that I can cut/paste and run.
dropping all orphaned users on a server:
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]; select ''DROP USER ''' + ', u.name from master..syslogins l right join sysusers u on l.sid = u.sid where l.sid is null and issqlrole <> 1 and isapprole <> 1 and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''system_function_schema'' and u.name <> ''sys'' and u.name <> ''dbo'')'
EXEC sp_MSforeachdb @command
thanks!
July 14, 2016 at 5:44 am
additionally, getting rid of the column name and --------- in the output so that i only have the drop statements would be a bonus.
July 14, 2016 at 6:49 am
This can get you a copy-paste-exec code. Be careful with it.
DECLARE @command varchar(1000) ;
SELECT @command = 'SELECT ''USE [?]'';
select ''DROP USER '' + QUOTENAME( u.name)
from master..syslogins l
right join [?]..sysusers u on l.sid = u.sid
where l.sid is null
and issqlrole <> 1
and isapprole <> 1
and u.name <> ''INFORMATION_SCHEMA''
and u.name <> ''guest''
and u.name <> ''system_function_schema''
and u.name <> ''sys''
and u.name <> ''dbo'''
EXEC sp_MSforeachdb @command;
Be careful with it.
July 14, 2016 at 7:44 am
Check out SET QUOTED_IDENTIFIER OFF. Now you no longer have to worry about how dang many single quotes you need to get your dynamic sql to be parsable! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2016 at 7:49 am
sweet! thanks guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply