Why is one of our databases accessible only to Admin users?

Regina Harter 25 Reputation points

We have a system that is currently set up to use SQL Server Express 2014. Due to the end of support, we are attempting to upgrade the system to use SQL Server Express 2017. This is being accomplished mainly by detaching the existing databases, uninstalling SQL Server Express 2014, installing SQL Server Express 2017, then attaching the same databases. For 6 of the 7 databases it works great, all accessible for both admin and nonadmin users. For the 7th one, while it is accessible to Admin users, when connecting as a nonadmin user no content is visible. Connecting to SQLCMD through command prompt, the connection to the database succeeds, but executing "select * from <dbname>.sys.tables" returns no rows. This same query shows about 25 tables as an Admin user. All 7 databases are actually created by a subcontrator. I've asked if there is something about this database the differs from the other 6, but they were unable to identify anything that might affect permissions between admin and nonadmin users. Searching the web for help has not located any similar situations. Any clues as to the underlying issues would be appreciated.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,568 questions
{count} vote

Accepted answer
  1. Erland Sommarskog 92,881 Reputation points

    Run this as admin in both the database where you have trouble and and one there is working.

    EXECUTE AS USER = 'abamgr'
    SELECT * FROM sys.user_token
    SELECT r.name
    FROM   sys.database_principals r
    JOIN   sys.database_role_members rm ON r.principal_id = rm.role_principal_id
    JOIN   sys.database_principals u ON u.principal_id = rm.member_principal_id
    WHERE  u.name = 'abamgr'
    SELECT *
    FROM   sys.database_permissions dp
    JOIN   sys.database_principals u ON u.principal_id = dp.grantee_principal_id
    WHERE  u.name = 'abamgr'

    Replace "abamgr" with the name of your non-admin user.

    Compare what is different between the databases. If there are no direct differences, then you may have to look at roles or Windows groups the user is a member of

    It is not practical for us to use the Installation Center for the upgrade. We are calling "SETUP.EXE" with a configuration.ini file to do the install. Can I just modify the configuration.ini to do the upgrade? I'm assuming at least the Action value should be updated.

    It appears that the /ACTION switch also accepts the option UPGRADE, although the documentation says otherwise. (Myself, I don't install SQL Server that often that I bother about using the command line, but I'm content with using the wizard.)

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful