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 🙂

Advertisements

Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.

problem:

a working replication setup suddenly started failing with below error ..nothing was changed from configuration aspect of

subscriber .. but the distributor could not connect to the subscriber .. moreover .. the connection to subscriber instance (VIA windows

authentication ) could not browse through properties ETC .. (SQL agent ETC)

error :

Error messages:
The process could not connect to Subscriber ‘subscriber_instance’. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084) Get help: http://help/MSSQL_REPL20084 Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection. (Source: MSSQLServer, Error number: 18452) Get help: http://help/18452

Solution:

1> login failed for user ‘NULL‘ is basically solved by switching the instance authentication mode to mixed from windows

(in my case it was already so)

2> in the eventvwr .. few kerberos warning and errors were logged in (which looked every cryptic)

3> instead of troubleshooting those .. i decided to bounce the SQL instance .. to see whether it clears the error

for above scenario .. it worked fine .. and NULL user errors were no longer appearing ,replication was working fine ..

other Scenario :

4> you may to change authentication mode to mixed ..

hope this helpful to you 🙂