Drop role, user, login

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks

For logins you can query sys.server_principals, for users you can query sys.database_principals or USER_ID() and for roles sys.database_principals. Ex:

-- logins (if you want to drop certificate based logins then you need to check for other types)

if exists(select * from sys.server_principals

where type IN ('S', 'U', 'G') and name = @.name)

begin

set @.name = quotename('somelogin')

exec('drop login ' + @.name)

end

-- users

if USER_ID(@.name) is not null

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- users

if exists(select * from sys.database_principals

where type IN ('S') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- roles

if exists(select * from sys.database_principals

where type IN ('R') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop role ' + @.name)

end

See the BOL "security catalog views" topic for more details.

|||Many thanks