Archive
what are my privileges or rights on an SQL instance
“Boot down pill” / Biohacks / SML.20130205.IPH5.10051 (Photo credit: See-ming Lee 李思明 SML)
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 🙂
how to add yourself to sysadmin role
one of the best thing about attending SQL Saturday’s is the people you meet and tricks you learn .. (hmmm ok .. even the food included)
i come across similar webcast were i learned below 2 methods to add yourself as sysadmin on any SQL instance ..
example : if the SQL installation was done by someone else and OS admins were never given sysadmins priviliges on SQL instance
so after you get yourself added to OS admin .. you can follow any of the below methods to achieve the same …
NOTE: both methods cause SQL restart .. so take a note of the same …
SQL Saturday 119 Lunch 017 (Photo credit: Michael Kappel)
International Bibliography of Periodical Literature (Photo credit: Wikipedia)
Option 1: http://archive.msdn.microsoft.com/addselftosqlsysadmin/ (the logged in user gets added in sysadmin group)
download the cmd file from above link and save it onto the server .. where you want to add yourself to sysadmin ..
double click the cmd file and it will ask for instance name .. default is taken as the SQL express edition instance …
only enter the instance name (example for instance node-1\SQL2012_INST …. we will just enter SQL2012_INST .. not the network name)
below messages will appear in the cmd window .. (Note : it will cause SQL to restart as it puts instance in single-user mode and restarts it twice )
Adding ‘NODE-1\test’ to the ‘sysadmin’ role on SQL Server instance ‘SQL2012_INST
1’.
Verify the ‘MSSQL$SQL2012_INST1’ service exists …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping.
The SQL Server (SQL2012_INST1) service was stopped successfully.
Start the ‘MSSQL$SQL2012_INST1’ service in maintenance mode …
Add ‘NODE-1\test’ to the ‘sysadmin’ role …
Stop the ‘MSSQL$SQL2012_INST1’ service …
The SQL Server (SQL2012_INST1) service is stopping…
The SQL Server (SQL2012_INST1) service was stopped successfully.
The SQL Server (SQL2012_INST1) service is starting.
The SQL Server (SQL2012_INST1) service was started successfully.
‘NODE-1\test’ was successfully added to the ‘sysadmin’ role.
Press any key to continue . . .
(you can explicitly choose which user/group is to be given sysadmin role, after you get yourself added in OS admin group)
Above is a manual method .. which has the same steps .. ie. Putting instance in single user and then adding a group or user to sysadmin role ..
enjoy 🙂
SQL DBA learning curve 