What is the "god" sys table?
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
开发者_StackOverflowUSER
ASYMMETRIC KEY
LOGIN
CERTIFICATE
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.
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
- USER =>
SELECT * FROM sys.sysusers
- ASYMMETRIC KEY =>
SELECT * FROM sys.asymmetric_keys
- LOGIN =>
SELECT * FROM sys.syslogins
- CERTIFICATE =>
SELECT * FROM sys.certificates
精彩评论