checking database permissions from master

• erics44

  SSCertifiable

  Points: 6150

  More actions

  April 24, 2014 at 2:02 am

  #284814

  hi

  i need to run a script on all databases on a server, the user i want to use doesnt have access to some of the databases

  i get the error

  The server principal "username" is not able to access the database "database name" under the current security context.

  i have tried to use the tables sys.database_permissions and sys.database_principals but i dont have permissions to use those either

  is there a way of checking permissions of a database from the master database? or is there another way of checking permissions?

  thanks in advance

• erics44

  SSCertifiable

  Points: 6150

  More actions

  April 24, 2014 at 5:09 am

  #1708367

  bump?

  if there was a way to add sysusers to sysdatabases in the master database that would help?

  thanks

• deepforest

  SSCommitted

  Points: 1780

  More actions

  April 24, 2014 at 6:42 am

  #1708382

  I'm afraid, you need to be in 'sysadmin' role to do that. Check 'print user_name()' and see what permissions that login has.

• sqlmunkee

  SSCommitted

  Points: 1949

  More actions

  April 24, 2014 at 9:02 am

  #1708436

  I've come across this a few times and it's usually down to the authentication process.

  Can you ask them to run the following code themselves (assuming they have access via SSMS) ?

  SELECT DISTINCT lt.name FROM sys.login_token lt INNER JOIN sys.server_principals sp ON sp.sid=lt.sid

  That'll tell you which login details they're using to get authenticated, and there could well be a mismatch somewhere.

  [font="Courier New"]sqlmunkee[/font]
  [font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]