SQL Server Security - miniDBA


Permissions List


Hovering the mouse over the permissions label in the status bar on the server level dashboard will show you the server level permissions the login currently being used to access the given instance.

SQL Server instance dashboard

VIEW SERVER STATE is the permission to look for here - without it miniDBA will not be able to query the server.

Do the same at the database level dashboard to see what permissions the given login has per database.

Databases will only be visible to miniDBA if the login being used by miniDBA has VIEW DATABASE STATE permission for each database.
miniDBA cannot tell you this information if it does not have VIEW DATABASE STATE so run this query to see which databases the current login has the permission for: 

    DECLARE @sql varchar(2000)
    DECLARE @db varchar(200)
    DECLARE @dbs3 table(name varchar(200))
    CREATE TABLE [#perms_{1}] (db varchar(200), [perm] varchar(100))
    INSERT @dbs3 (name) select name from sys.sysdatabases WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1

    SET @db = (select top(1) name from @dbs3  )
    print @db
    WHILE @db is not null
    BEGIN
    set @sql = 'use [' + @db + ']
    INSERT [#perms_{1}] SELECT db_name(), permission_name FROM fn_my_permissions(NULL, ''DATABASE'')'

    exec(@sql)

    delete top (1) from @dbs3
    set @db = (select top(1) name from @dbs3)
    END
    SELECT * FROM [#perms_{1}]
    WHERE perm = 'VIEW DATABASE STATE'

    DROP TABLE [#perms_{1}]