December 18, 2009 at 2:16 pm
Running SQL2008 on Windows Server standard sp2 64bit
On user PC odbc driver system dsn -
the data source is only showing the default db rather than all db's the user has permission to.
However, the odbc driver system dsn that was create for sql2000 32bit does display all db's the user has permission to.
I've looked for days but cannot find any settings in SQL2008 that would indicate or allow access to just the default db.
example:
user1 has read permission to db1, db2 and db3.
the default db in the security logins is db1, go to user PC create a odbc connection and it only displays db1.
Does anyone have any idea what the issue is?:crying:
December 22, 2009 at 10:52 am
Have you checked to see if the VIEW ANY DATABASE permision has been denied or if any other permissions you may take for granted have been denied to the particular user?
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
December 22, 2009 at 10:58 am
Where would I find the security for 'View any database'?
This maybe my issue.
December 22, 2009 at 11:37 am
That was the issue, and is fixed.
December 22, 2009 at 11:48 am
In Management Studio expand Security\Logins\ Right click on the login in question and select Properties from the pop-up window
Under the securables tab click Search. Select Server.
In the window that pops up look for anything selected to get an idea of what rights are granted or revoked.
Alternately you can use T/SQL:
SELECT SP.[name], SSP.[permission_name], SSP.[state_desc]
FROM sys.server_principals SP INNER JOIN sys.server_permissions SSP ON SP.[principal_id] = SSP.[grantee_principal_id]
WHERE SP.[name] = 'foo'
Replacing 'foo' with the login name in question.
SELECT SP.[name], SSP.[permission_name], SSP.[state_desc]
FROM sys.server_principals SP INNER JOIN sys.server_permissions SSP ON SP.[principal_id] = SSP.[grantee_principal_id]
WHERE SP.[name] = 'foo'
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
December 22, 2009 at 12:13 pm
Thank you Tim that was my issue.
I was trying to eliminate the sys and schema's from displaying on the tables so the users would not get confused.
Which I still have not figured out!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply