ansaurus

Question

Answer 1

+1 A:

What do you mean by view the database?!

you can use the following query to list all the permissions on current database:

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

Wael Dalloul 2010-05-27 07:41:10

Yes that's kind of what I want to do - but maybe for a database you aren't connected to? Is that possible?

J Harley 2010-05-27 07:48:19

Answer 2

+3 A:

Firstly, you'll need to find out all of the names in your DB server:

SELECT [name]
  FROM sys.databases;

Then, you'll have to run the following command for each database in order to get the permissions:

USE databaseName;

SELECT *
  FROM fn_my_permissions(null, 'DATABASE')

Ardman 2010-05-27 07:57:20

Hello - that seems to work great - however, what would I replaced @dbname with?

J Harley 2010-05-27 08:18:12

@J Harley: My bad. I forgot to change that to 'DATABASE'.

Ardman 2010-05-27 08:31:45

Perfect that works fine - cheers :)

J Harley 2010-05-27 08:37:44

It is a good solution...

ccppjava 2010-05-27 08:46:43

Answer 3

A:

I think you want to look at sys.database_permissions where I think you can query this information.

ho1 2010-05-27 08:03:19

SQL Server checking if you have access to a database