CODEWITHSUNDEEP
sql-server-2005
J Harley
J Harley

Reputation: 285

SQL Server checking if you have access to a database

Good Morning,

I have connected to my SQL Server 2005 - and have managed to list all of the databases on that server. Now I would like to run a check on each database to see if I have permission to view that database..

How would I go about this?

Many Thanks, Joel

Upvotes: 1

Views: 13276

Answers (4)

Jonathan
Jonathan

Reputation: 26619

As neslekkiM has pointed out in the comments of a different answer, you can use this inline when finding out the list of databases:

SELECT name, has_dbaccess(name) 
FROM sys.databases

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48547

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')

Upvotes: 0

Hans Olsson
Hans Olsson

Reputation: 55009

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

See here for more info:
http://msdn.microsoft.com/en-us/library/ms188367.aspx

Upvotes: 0

Wael Dalloul
Wael Dalloul

Reputation: 23014

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');

Upvotes: 1

Related Questions