Archive
Posts Tagged ‘Login’
what are my privileges or rights on an SQL instance
April 9, 2013
1 comment
whoops!!! it happens we are given certain rights on SQL instance .. we tend to ask “WHAT are MY RIGHTS on THIS instance ??? ”
and you may be said “WELL FIND IT OUT yourSELF”
then we search for a PILL .. which could help us on the spot ..
fn_my_permissions is similar to the PILL .. that could work wonders for you ..
the results you get after running this will vary according to your access level on an instance
select * from fn_my_permissions (NULL,’SERVER’) — gives access details at server level
select * from fn_my_permissions (NULL,’Database”) — gives access details at DB level
list of privileges if your are sysadmin on a SQL server instance
select * from fn_my_permissions (NULL,’SERVER’)
entity_name | subentity_name | permission_name |
server | CONNECT SQL | |
server | SHUTDOWN | |
server | CREATE ENDPOINT | |
server | CREATE ANY DATABASE | |
server | ALTER ANY LOGIN | |
server | ALTER ANY CREDENTIAL | |
server | ALTER ANY ENDPOINT | |
server | ALTER ANY LINKED SERVER | |
server | ALTER ANY CONNECTION | |
server | ALTER ANY DATABASE | |
server | ALTER RESOURCES | |
server | ALTER SETTINGS | |
server | ALTER TRACE | |
server | ADMINISTER BULK OPERATIONS | |
server | AUTHENTICATE SERVER | |
server | EXTERNAL ACCESS ASSEMBLY | |
server | VIEW ANY DATABASE | |
server | VIEW ANY DEFINITION | |
server | VIEW SERVER STATE | |
server | CREATE DDL EVENT NOTIFICATION | |
server | CREATE TRACE EVENT NOTIFICATION | |
server | ALTER ANY EVENT NOTIFICATION | |
server | ALTER SERVER STATE | |
server | UNSAFE ASSEMBLY | |
server | ALTER ANY SERVER AUDIT | |
server | CONTROL SERVER |
select * from fn_my_permissions (NULL,’DATABASE’)
entity_name | subentity_name | permission_name |
server | CONNECT SQL | |
server | SHUTDOWN | |
server | CREATE ENDPOINT | |
server | CREATE ANY DATABASE | |
server | ALTER ANY LOGIN | |
server | ALTER ANY CREDENTIAL | |
server | ALTER ANY ENDPOINT | |
server | ALTER ANY LINKED SERVER | |
server | ALTER ANY CONNECTION | |
server | ALTER ANY DATABASE | |
server | ALTER RESOURCES | |
server | ALTER SETTINGS | |
server | ALTER TRACE | |
server | ADMINISTER BULK OPERATIONS | |
server | AUTHENTICATE SERVER | |
server | EXTERNAL ACCESS ASSEMBLY | |
server | VIEW ANY DATABASE | |
server | VIEW ANY DEFINITION | |
server | VIEW SERVER STATE | |
server | CREATE DDL EVENT NOTIFICATION | |
server | CREATE TRACE EVENT NOTIFICATION | |
server | ALTER ANY EVENT NOTIFICATION | |
server | ALTER SERVER STATE | |
server | UNSAFE ASSEMBLY | |
server | ALTER ANY SERVER AUDIT | |
server | CONTROL SERVER |
Enjoy 🙂
Advertisements
Categories: General
Authentication, Login, Microsoft SQL Server, SQL