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

Question

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

Regina Harter 25

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.

Erland Sommarskog 92,881 Reputation points

2023-10-26T21:15:56.23+00:00
First of all, your upgrade seems very awkward to me from two accounts:

You can do an in-place upgrade, rather than an uninstall and a re-install.

SQL 2017 is also out of mainstream support. SQL 2019 or SQL 2022 would have been better choices.

As for the issue you are seeing, this is the default behaviour. That is, if you have not been granted any permissions, you will not be able to see any tables, views etc.

I'm a little worried that your unorthodox method of upgrading has caused a mismatch between server logins and database users. Are you using Windows authentication or SQL authentication?

When you login as a non-admin user and run

USE ProblemDB go SELECT SYSTEM_USER, USER

what do you see?
Regina Harter 25 Reputation points

2023-10-30T15:29:22.4666667+00:00

Hi Erland,

Thanks for your comment. We are sticking with 2017 because this system is related to ones that are already on 2017, we just need to upgrade this one to match. We don't have anything on 2019, and we have one being put together with 2022, but since that requires an upgrade to not only this system but any connected components to .NET 4.6.2 that's out of the question for anything currently in operation, as this is.

As for this being default behavior, that doesn't make much sense to me. It's only one out of the 7 databases that is having this problem, they have all been granted the same permissions, and even the problem one worked fine in 2014.

We are using Windows authentication. I have not seen any information on doing an in-place upgrade. Does that mean I can just install 2017 without uninstalling 2014? Will I still have to reattach the databases afterward, or do the attachments remain intact after the upgrade?

Thanks,

Regina
Erland Sommarskog 92,881 Reputation points

2023-10-30T22:05:36.06+00:00
As for this being default behavior, that doesn't make much sense to me. It's only one out of the 7 databases that is having this problem, they have all been granted the same permissions, and even the problem one worked fine in 2014.

The problem is that I don't know your environment and how you have set up the permissions. But I know that if you do not set any permissions at all, well, there are no permissions.

I asked for the output of this query when you are logged in as the problematic user:

USE ProblemDB go SELECT SYSTEM_USER, USER

We need to see the output to be able to help with further troubleshooting.

have not seen any information on doing an in-place upgrade. Does that mean I can just install 2017 without uninstalling 2014?

Yes, see below. (The image is from the SQL 2022 Setup, but this has not changed for many versions now).

Will I still have to reattach the databases afterward, or do the attachments remain intact after the upgrade?

When you do an in-place upgrade, the databases will remain attached. The only thing you may want to do is to change the compatibility level to match the version you have installed.
Regina Harter 25 Reputation points

2023-10-30T23:59:21.0166667+00:00

Thanks Erland,

Sorry, I needed to redo my system to include the upgrade in order to answer your question. SELECT SYSTEM_USER, USER shows <computername><username>, <computername><username> for the problem database, but it is the same thing it returns for the other databases.

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.

Thanks,

Regina

Erland Sommarskog 92,881 Reputation points

2023-10-26T21:15:56.23+00:00

First of all, your upgrade seems very awkward to me from two accounts:

You can do an in-place upgrade, rather than an uninstall and a re-install.

SQL 2017 is also out of mainstream support. SQL 2019 or SQL 2022 would have been better choices.

As for the issue you are seeing, this is the default behaviour. That is, if you have not been granted any permissions, you will not be able to see any tables, views etc.

I'm a little worried that your unorthodox method of upgrading has caused a mismatch between server logins and database users. Are you using Windows authentication or SQL authentication?

When you login as a non-admin user and run

USE ProblemDB go SELECT SYSTEM_USER, USER

what do you see?
Regina Harter 25 Reputation points

2023-10-30T15:29:22.4666667+00:00

Hi Erland,

Thanks for your comment. We are sticking with 2017 because this system is related to ones that are already on 2017, we just need to upgrade this one to match. We don't have anything on 2019, and we have one being put together with 2022, but since that requires an upgrade to not only this system but any connected components to .NET 4.6.2 that's out of the question for anything currently in operation, as this is.

As for this being default behavior, that doesn't make much sense to me. It's only one out of the 7 databases that is having this problem, they have all been granted the same permissions, and even the problem one worked fine in 2014.

We are using Windows authentication. I have not seen any information on doing an in-place upgrade. Does that mean I can just install 2017 without uninstalling 2014? Will I still have to reattach the databases afterward, or do the attachments remain intact after the upgrade?

Thanks,

Regina
Erland Sommarskog 92,881 Reputation points

2023-10-30T22:05:36.06+00:00

As for this being default behavior, that doesn't make much sense to me. It's only one out of the 7 databases that is having this problem, they have all been granted the same permissions, and even the problem one worked fine in 2014.

The problem is that I don't know your environment and how you have set up the permissions. But I know that if you do not set any permissions at all, well, there are no permissions.

I asked for the output of this query when you are logged in as the problematic user:

USE ProblemDB go SELECT SYSTEM_USER, USER

We need to see the output to be able to help with further troubleshooting.

have not seen any information on doing an in-place upgrade. Does that mean I can just install 2017 without uninstalling 2014?

Yes, see below. (The image is from the SQL 2022 Setup, but this has not changed for many versions now).

Will I still have to reattach the databases afterward, or do the attachments remain intact after the upgrade?

When you do an in-place upgrade, the databases will remain attached. The only thing you may want to do is to change the compatibility level to match the version you have installed.
Regina Harter 25 Reputation points

2023-10-30T23:59:21.0166667+00:00

Thanks Erland,

Sorry, I needed to redo my system to include the upgrade in order to answer your question. SELECT SYSTEM_USER, USER shows <computername><username>, <computername><username> for the problem database, but it is the same thing it returns for the other databases.

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.

Thanks,

Regina

Answer 1

Erland Sommarskog 92,881

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

EXECUTE AS USER = 'abamgr'
go
SELECT * FROM sys.user_token
go
REVERT
go
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'
go
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.)

Regina Harter 25 Reputation points

2023-11-01T18:34:06.11+00:00

Hi Erland,

I attempted to run the scripts you provided, but they return nothing because none of my windows user names appear in the sys.database_principals view. The group my Admin user belongs to (dbo) does appear, but it does not look like my non-admin user belongs to any groups. However, as I mentioned, I can list tables from all databases except the problem one as the non-admin user, and even from the problem one as the admin user. I know they should be listed in one of the tables/views (can't remember which one at the moment) as a "WINDOWS_USER", but I could not find any records with that designation. Let me know what you think this means.

Thanks, Regina
Regina Harter 25 Reputation points

2023-11-01T18:39:22.43+00:00

I take some of that back. The SELECT * FROM SYS.USER_TOKENS does return rows and they do differ between the databases that work and the one that doesn't. The one that doesn't is missing an entry for db_owner.

Thanks, Regina
Erland Sommarskog 92,881 Reputation points

2023-11-01T21:45:28.01+00:00

I don't recall if I mentioned it, but it is a challenge to help people troubleshooting issues like this, since I have very little information. For instance, I don't know if you have granted access directly to the user, or to role or a Windows AD group. But to get you going, I had to start somewhere.

Then again, the list from sys.user_token should give you the list of AD groups the user is a member of.

And, yes, if that AD group has been added to db_owner, db_owner will also list among the tokens. (And db_owner is pretty much admin-level with in the database.)
Regina Harter 25 Reputation points

2023-11-01T23:57:30.34+00:00

Why would the list of AD groups the user is a member of vary by which database is being used? There is no where that I can detect that the databases themselves are being added to any specific groups or granted to specific users or roles.
Regina Harter 25 Reputation points

2023-11-02T00:00:04.25+00:00

Forgive me, I don't know a lot about Active Directory. Why would the AD groups vary for a user based on which Database is used? I don't see anywhere that the databases are granted specifically to any groups or users.
Erland Sommarskog 92,881 Reputation points

2023-11-02T22:14:21.8166667+00:00

The AD groups should definitely be the same in all databases, anything else would be spooky.

However, the AD groups may have been granted different permissions or role membership in different databases.
Regina Harter 25 Reputation points

2023-11-03T16:47:22.73+00:00

Got all the configuration options properly set, and the in place upgrade from SQL Server Express 2014 to SQL Server Express 2017 worked. All databases are now accessible for both Admin and Non Admin users.
Erland Sommarskog 92,881 Reputation points

2023-11-03T22:05:57.6566667+00:00

A final comment: When I suggested above the documentation did say that UPGRADE was a valid value for /ACTION I was wrong; I was not reading the page https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt in full. There are many parameters tables on this page. I was in touch with one of the writers about this, and as a result of my feedback, they have added a passage just before the first parameter table to make clear that there are many.

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

0 additional answers

Activity