none
sQL server Permission Error:- Unable to access database

    Question

  • Hi friends,

    When I login to my sql server it gives me error " The database is not accessible" , also I am unable to see Sql server Agent

    My login id is part of one AD group which is added in SQL server login & I use windows authentication also i can see connect sql, view any database & view any defination permission is assigned.

    I think this is permission issue which permissions i need to complete login in sql server (except sysadmin role).

    Thursday, December 27, 2012 1:47 PM

Answers

  • The CONTROL SERVER and sysadmin fixed server role have very similar permissions, but they are not identical. When a login has CONTROL SERVER, it can still be blocked from some activities by a DENY permission. But sysadmin doesn't get blocked by a DENY. If you have CONTROL SERVER on the instance of SQL Server but cannot access a database, you might be blocked by a DENY CONNECT permission in that database. Someone who is a sysadmin could check by executing

    SELECT pr.type_desc, pr.name,
     isnull (pe.state_desc, 'No permission statements') AS state_desc,
     isnull (pe.permission_name, 'No permission statements') AS permission_name
     FROM sys.database_principals AS pr
     LEFT OUTER JOIN sys.database_permissions AS pe
       ON pr.principal_id = pe.grantee_principal_id
     WHERE pr.is_fixed_role = 0
     ORDER BY pr.name, type_desc;

    It has to be executed from within the database, so you can't do it, since you can't get in.

    Some procedures, including some of the DBCC commands check for membership in the sysadmin fixed server role. Having the CONTROL SERVER permission will not let you past that check. You must actually be a member of the sysadmin fixed server role.

    The VIEW SERVER STATE permission is sufficient to monitor many of the dynamic management views. Your CONTROL SERVER permission includes the VIEW SERVER STATE permission. So you might be able to find the information you need in the DMV's.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:57 AM
    Friday, January 04, 2013 10:43 PM
  • I missed that Mayur had been granted CONTROL SERVER. While as Rick says, it is not equal to sysadmin, it's enough. Since if you have CONTROL SERVER you can add yourself to sysadmin...

    What does this query return:

    select * from sys.fn_my_permissions(NULL, NULL)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:58 AM
    Friday, January 04, 2013 11:25 PM
  • The result of above query.  includes following permissions     CONNECT SQL, SHUTDOWN,ALTER ANY LOGIN,ALTER ANY DATABASE,CONTROL SERVER etc.

    I think RIck has post  very good & important post  Though I hv control server permission , i am unable to access database i.e. definitely there is deny permissions.

    But One thing is proved that  'control server' & SQLAgentoperatorRole' permissions are enough for daily operations if you don't have sysadmin priviledges.

    Thanks Rick & erland for your help.

           
    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:57 AM
    Wednesday, January 09, 2013 9:56 AM

All replies

  • "Database is not accessible" sounds more like a problem with the database. Using Object Explorer in Management Studio, is the database greyed out? Does the icon look like the others? Can you right-click on the database, select Properties, and then on the Options tab, what is the Database State (listed near the bottom).

    However, not being able to see the SQL Server Agent might be a permissions issue.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, December 27, 2012 4:15 PM
  • Hi Mayur,

    Please verify the login has permission on that database. You can check it by going To Security --> Login-->Right and Click Property and then go to user mapping tab.

    For more information, you can have a look at the following links.

    http://www.mssqltips.com/sqlservertip/2038/understanding-how-a-user-gets-database-access-in-sql-server/

    Regards,

    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, January 02, 2013 3:43 AM
  • The agent which you are not able to view is the permission issue that you have or it might be an express edition of SQL.

    The database is not accessible"

    >>This is also due to either the permission issues where you will not have permissions to access the database or it might be inaccessible due to

    non operational mode.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Wednesday, January 02, 2013 4:26 AM
  • Hi there,

    open your SQL server configuration manager and see which account is it using to run your services.

    login using admin account and see if you can access the database. If you able to access then the user you are trying to connect may have  permissions issue.

    your agent must appear if you install agent services.

    Good luck

    kumar

    Wednesday, January 02, 2013 4:35 AM
  • I have provided the following permissions now i can see sql server agent but unable to access database

    grant control server to [sysuser]

    sp_addrolemember 'SQLAgentoperatorRole','sysuser'

    I think this is windows login permission issue?

    Error:- No permission to access database 'database name'


    • Edited by Mayur-DEW Thursday, January 03, 2013 4:58 PM
    Thursday, January 03, 2013 4:54 PM
  • I am interested in seeing the results of this query to confirm you are connected as a sysadmin and database owner.

    SELECT suser_sname() AS Login, user AS DatabaseUser, IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin,
    IS_ROLEMEMBER('db_owner') AS IsDbOwner;

    I expect it will return your login, then 'dbo', then 1, and 1 which confirms you are member of those groups.

    Also, the following query should confirm the database in online.

    SELECT name, state_desc FROM sys.databases;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, January 04, 2013 5:05 PM
  • Run above queries

    Result:-

    login name= ss\sysuser, database user= ss\sysuser ,is sysadmin=0, is dbowner=0, all databases are online

    I am not member of sysadmin group, even it won't be provided to me for some time, so I need such a permissions  with the help of that I can perform daily dba activities like  monitoring.

    I need to specify permissions which i required so that I can request it.  AS is stated earlier control server permission &sp_addrolemember 'SQLAgentoperatorRole' is given to me but it dosen't work.


    Friday, January 04, 2013 10:28 PM
  • If I understand this thread correctly, it seems that you are simply not added as a user in that particular database. You will need to talk to someone who has db_owner or db_securityadmin in that database.

    If you want carte blanche-access to all databases on the server, you need CONTROL SERVER, which is more or less the same as sysadmin membership.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 04, 2013 10:37 PM
  • The CONTROL SERVER and sysadmin fixed server role have very similar permissions, but they are not identical. When a login has CONTROL SERVER, it can still be blocked from some activities by a DENY permission. But sysadmin doesn't get blocked by a DENY. If you have CONTROL SERVER on the instance of SQL Server but cannot access a database, you might be blocked by a DENY CONNECT permission in that database. Someone who is a sysadmin could check by executing

    SELECT pr.type_desc, pr.name,
     isnull (pe.state_desc, 'No permission statements') AS state_desc,
     isnull (pe.permission_name, 'No permission statements') AS permission_name
     FROM sys.database_principals AS pr
     LEFT OUTER JOIN sys.database_permissions AS pe
       ON pr.principal_id = pe.grantee_principal_id
     WHERE pr.is_fixed_role = 0
     ORDER BY pr.name, type_desc;

    It has to be executed from within the database, so you can't do it, since you can't get in.

    Some procedures, including some of the DBCC commands check for membership in the sysadmin fixed server role. Having the CONTROL SERVER permission will not let you past that check. You must actually be a member of the sysadmin fixed server role.

    The VIEW SERVER STATE permission is sufficient to monitor many of the dynamic management views. Your CONTROL SERVER permission includes the VIEW SERVER STATE permission. So you might be able to find the information you need in the DMV's.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:57 AM
    Friday, January 04, 2013 10:43 PM
  • I missed that Mayur had been granted CONTROL SERVER. While as Rick says, it is not equal to sysadmin, it's enough. Since if you have CONTROL SERVER you can add yourself to sysadmin...

    What does this query return:

    select * from sys.fn_my_permissions(NULL, NULL)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:58 AM
    Friday, January 04, 2013 11:25 PM
  • The result of above query.  includes following permissions     CONNECT SQL, SHUTDOWN,ALTER ANY LOGIN,ALTER ANY DATABASE,CONTROL SERVER etc.

    I think RIck has post  very good & important post  Though I hv control server permission , i am unable to access database i.e. definitely there is deny permissions.

    But One thing is proved that  'control server' & SQLAgentoperatorRole' permissions are enough for daily operations if you don't have sysadmin priviledges.

    Thanks Rick & erland for your help.

           
    • Marked as answer by Mayur-DEW Wednesday, January 09, 2013 9:57 AM
    Wednesday, January 09, 2013 9:56 AM