I wanted to see my permissions on SQL Server and Googling it was proving very hard. So I’m adding this here in case someone might find it useful. This will look at all the databases on the current server and list all of your permissions for each. As a bonus, it will list the current server as well.<\/p>\n
[sql]
\nDECLARE @myserver sysname;
\nSET @myserver = (
\n SELECT
\n \ts.name
\n FROM
\n \tsys.servers s
\n WHERE
\n \ts.server_id = 0
\n );<\/p>\n
SELECT
\n\t@myserver as ‘Server’
\n, d.name AS ‘Database’
\n, fbp.permission_name AS Permission
\nFROM
\n\t(
\n\t SELECT
\n\t \t‘DATABASE’ AS mytype
\n\t , *
\n\t FROM
\n\t \tsys.databases
\n\t) d
\n JOIN sys.fn_builtin_permissions(null) fbp
\n ON d.mytype = fbp.class_desc
\nWHERE
\n\tHas_perms_by_name(quotename(d.name) , ‘database’ , fbp.permission_name) = 1
\nORDER BY
\n\td.name
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"
I wanted to see my permissions on SQL Server and Googling it was proving very hard. So I’m adding this here in case someone might find it useful. This will look at all the databases on the current server and … Continue reading