0
IF  EXISTS (SELECT * FROM sys.all_objects WHERE name = N'SOMELOGIN')
DROP USER [SOMELOGIN]
GO

Does not work because USER SOMELOGIN does not live in sys.all_objects.

Is there a global "god" table I can look in to see if something exists. (i.e. dropping it when it doesn't exist doesn't throw an error)

Alternatively is there an online resource for finding out where certain types of objects live?

I need to drop the following

  • USER
  • ASYMMETRIC KEY
  • LOGIN
  • CERTIFICATE
Kara
  • 6,115
  • 16
  • 50
  • 57
Raynos
  • 166,823
  • 56
  • 351
  • 396

3 Answers3

1

sys.objects is used for objects in a database, such as tables, stored procedures, views etc.

I think you need the following tables:

SELECT * FROM sys.sysusers
WHERE [name] = 'someUser'

SELECT * FROM sys.asymmetric_keys
WHERE [name] = 'someKey'

SELECT * FROM sys.certificates
WHERE [name] = 'someCertificate'

SELECT * FROM sys.syslogins
WHERE [name] = 'someLogin'

EDIT

The nearest thing I can find for detailing the system views is this. It splits them out by type of view. For instance, drilling down to Catalog Views > Security Catalog Views will give you the views for security related views e.g. sys.asymmetric_keys

I'm not aware of anything that will give you a Logins > sys.syslogins type of mapping.

codingbadger
  • 42,678
  • 13
  • 95
  • 110
1

I hope this link might be useful. All views you need are under Security Catalog Views. Also, you probably need to query sys.database_principals instead of obsolete sysusers and syslogins

a1ex07
  • 36,826
  • 12
  • 90
  • 103
0
  • USER => SELECT * FROM sys.sysusers
  • ASYMMETRIC KEY => SELECT * FROM sys.asymmetric_keys
  • LOGIN => SELECT * FROM sys.syslogins
  • CERTIFICATE => SELECT * FROM sys.certificates
cdel
  • 717
  • 7
  • 14