A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking their Sr. DBA the question about permissions.
I run following two queries to know what are the permissions I am assigned at Server level and Database level.
SELECT * FROM fn_my_permissions(NULL, 'SERVER'); USE AdventureWorks; SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); GO
Results were interesting. After I came back from my visit, I ran above script on my personal database server, where I logged as SA.
Resultset for 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 CONTROL SERVER
ResultSet for Database:
entity_name subentity_name permission_name
database CREATE TABLE
database CREATE VIEW
database CREATE PROCEDURE
database CREATE FUNCTION
database CREATE RULE
database CREATE DEFAULT
database BACKUP DATABASE
database BACKUP LOG
database CREATE DATABASE
database CREATE TYPE
database CREATE ASSEMBLY
database CREATE XML SCHEMA COLLECTION
database CREATE SCHEMA
database CREATE SYNONYM
database CREATE AGGREGATE
database CREATE ROLE
database CREATE MESSAGE TYPE
database CREATE SERVICE
database CREATE CONTRACT
database CREATE REMOTE SERVICE BINDING
database CREATE ROUTE
database CREATE QUEUE
database CREATE SYMMETRIC KEY
database CREATE ASYMMETRIC KEY
database CREATE FULLTEXT CATALOG
database CREATE CERTIFICATE
database CREATE DATABASE DDL EVENT NOTIFICATION
database CONNECT
database CONNECT REPLICATION
database CHECKPOINT
database SUBSCRIBE QUERY NOTIFICATIONS
database AUTHENTICATE
database SHOWPLAN
database ALTER ANY USER
database ALTER ANY ROLE
database ALTER ANY APPLICATION ROLE
database ALTER ANY SCHEMA
database ALTER ANY ASSEMBLY
database ALTER ANY DATASPACE
database ALTER ANY MESSAGE TYPE
database ALTER ANY CONTRACT
database ALTER ANY SERVICE
database ALTER ANY REMOTE SERVICE BINDING
database ALTER ANY ROUTE
database ALTER ANY FULLTEXT CATALOG
database ALTER ANY SYMMETRIC KEY
database ALTER ANY ASYMMETRIC KEY
database ALTER ANY CERTIFICATE
database SELECT
database INSERT
database UPDATE
database DELETE
database REFERENCES
database EXECUTE
database ALTER ANY DATABASE DDL TRIGGER
database ALTER ANY DATABASE EVENT NOTIFICATION
database VIEW DATABASE STATE
database VIEW DEFINITION
database TAKE OWNERSHIP
database ALTER
database CONTROL
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
8 Comments. Leave new
Not in Books on Line either! cool.
Hi ,
I have a Table of Customer. And i want to have a triger on this table to see who update or inserted the row in the table.
But the problem is that how can i get the Sql user Id/name who have edit or added row to the table.
Any clue.
Dear pinal dave,
currently i am working as a .Net website developer but if i want to come to the sql server side then can you suggest me all areas in SQL Server so that i can select any of them and consrntrait for it. one of my friend working in Excellence has given me your reference. please suggest me for SQL server side areas.
Thanks & Regards,
Vaibhav
SQL Server 2005
This is what I get:
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘fn_my_permissions’.
You can always use sp_helplogins.This will give you list of username,database and rights assigned to it.Give you result something like below:
Login name Database username(Actually rights assigne)
Abc master Data Reader
Note:You need to filter out usernames
I did not get any results, the result pane shows blank after running the qry…plz let me knw…
Many of them went on to undertake their own pit bulls.
How can I Create ASSEMBLY for multiple databases at a time its urgent
CREATE ASSEMBLY myAssembly
AUTHORIZATION dbo
FROM ‘C:\projects\bin\Releases\myAssembly.dll’
WITH PERMISSION_SET = SAFE
GO