HAS_ACCESS help

Question

HAS_ACCESS help

Joel Ewald

SSCertifiable

Points: 6120
More actions
November 17, 2010 at 2:00 pm

#231716

I have a server level login that I have given DB_DataReader, DB_DataWriter, and Public access to on a single specific database.
When I connect using the login and run the folloiwng code to get a list of databases the user has access to the database is not returned.
If I place the login in the db_owner group it does return that the login has access, but I do not want the login in that group.
SELECT [Name] as DatabaseName
from master.dbo.sysdatabases
WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1
AND [name] NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY [Name]
SELECT [Name] as DatabaseName
from master.dbo.sysdatabases
WHERE ISNULL(HAS_PERMS_BY_NAME([name], 'DATABASE', 'ANY'),0) = 1
AND [name] NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY [Name]
The login definately has access because I can navigate to the database and run queries against it.
I do not want to write a function to loop over all the databases checking
IS_MEMBER ('db_DataReader') =1 AND IS_MEMBER ('db_DataWriter') =1
I know there has to be an easy way to return a list of databases a login has access to.
Anyone have ideas?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

You are selecting from tables in master, does the user have db_datareader in master?

If not, create a stored procedure to return the databases and give that user execute permission on it. Maybe I'm not quite sure what you are trying to accomplish.

The probability of survival is inversely proportional to the angle of arrival.

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 2

I am just trying to get a list of user databases that the login has access to.

They are db_datareader and db_datawriter on the user database.

HAS_ACCESS returns a 0 unless I also give the login db_owner on the user database eventhough it has access through db_datareader and db_datawriter.

Steps:

Create a user database

Create a server level login

give it db_datareader, db_datawriter, and public for the user database.

now I want to call the server and get the user database returned as one that the login has access to.

Hope that helps.

Thanks

sturner SSC-Insane Points: 22411 More actions · Answer 3

I'm not sure what you mean by this: "now I want to call the server and get the user database returned as one that the login has access to."

Are you doing that using the login in question or with another login and what SQL are you executing when you do that?

The probability of survival is inversely proportional to the angle of arrival.

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 4

sturner (11/18/2010)
I'm not sure what you mean by this: "now I want to call the server and get the user database returned as one that the login has access to."
Are you doing that using the login in question or with another login and what SQL are you executing when you do that?

Yes. I am calling the database with the login in question.

As far as what SQL am I using ... that is the whole point of the post. I am trying to figure out the sql that will return me the list of databases the login has access to when the login had db_datareader and db_datawriter priviliges.

I have tried the statements in the original post.

For now I will just loop over the databases and check permissions for each since has_access does not appear to work without db_owner permissions.

I just did not want to resort to looping over the databases.

Thanks

sturner SSC-Insane Points: 22411 More actions · Answer 5

You will have to query the sys.sysusers table in _each_ database to determine if the particular user has access. This is further complicated by the fact that the login can be mapped to a different user name in a given db.

In any case, this is how you can check to see if a user has access in a given database:

if exists(select 1 from sys.sysusers where hasdbaccess = 1 and name = 'whateverusername')

print 'has access'

The probability of survival is inversely proportional to the angle of arrival.