Unable to grant membership to public role

Question

I'm trying to refresh my dev db with a snapshot from prod. I've restored the production db from the prod server to my dev server just named as development. I'm trying to grant access to the dev db to the user login that the website uses. But when I try to run ALTER ROLE [public] ADD MEMBER [mywebuser], I get the error

Msg 15081, Level 16, State 1, Line 16
Membership of the public role cannot be changed.

Thinking back, I believe when I ran CREATE USER [mywebuser] FOR LOGIN [mywebuser] on that database, I think it said it was already there. So I just moved on to granting access. There is a user and login by the same name on the prod server. Is it possible it has gotten confused by the name? If so, how can I check for it?

My dev server is running Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Developer Edition on Windows Server 2019 Datacenter 10.0 (X64)

My Prod server is running Microsoft SQL Server Web (64-bit) 15.0.2080.9 on Windows Server 2019 Datacenter 10.0 (X64)

Answer 1

I was able to get it working by removing the user from the database first and then recreating it. In my case, this login was used in other databases, so I couldn't drop the login. And droping the user didn't work immediately because it also owned some schema.

First, I had to find which schema it owned:

use [development]
go
--- See which schemas the user owns:
SELECT 
  S.*,
  [SchemaOwnwer]=dp.name,
  dp.type_desc
FROM SYS.schemas S
INNER JOIN SYS.database_principals dp ON S.principal_id = dp.principal_id

It showed that the user owned 2 schemas, so next I re-assign those to dbo:

-- change them so that dbo owns them:
alter authorization on schema::HangFire to dbo;
go
alter authorization on schema::db_owner to dbo;
go

I also had to remove the user from a few roles:

exec sp_droprolemember 'db_owner', 'mywebuser';
go
exec sp_droprolemember 'db_datareader', 'mywebuser';
go
exec sp_droprolemember 'db_datawriter', 'mywebuser';
go

Finally, I can drop the user

drop user [mywebuser]

Now, at last, I can add it back with proper settings:

create user [mywebuser] for login [mywebuser];
go

exec sp_addrolemember 'db_owner', [mywebuser];
go
exec sp_addrolemember 'db_datareader', [mywebuser];
go
exec sp_addrolemember 'db_datawriter', [mywebuser];
go

ALTER USER [mywebuser] WITH DEFAULT_SCHEMA=[dbo];
go
ALTER ROLE [db_owner] ADD MEMBER [mywebuser];
go

grant connect to [mywebuser];
go

Answer 2

The problem you have because of the different SIDs of sql login on PROD and DEV.

You can see the sql users with "missing" SIDs using the following command:

exec sp_change_users_login 'report'

Or just run sp_helpuser procedure and check for usernames with missing LoginName.

The issue can be fixed using the same proc sp_change_users_login :

exec sp_change_users_login 'update_one','user','login'

Answer 3

The underlying issue is that there is a difference between a SQL Server Login and a Database User.

A SQL Server Login can be either a native SQL Server Login or a Windows Authenticated User / Group.

A SQL Server Login authenticates against the SQL Server. After the login the SQL Server looks if the SQL Server Login is associated with a Database User in the database it is trying to access.

The Database User has the privileges to modify or view data in the database. If a link exists, then the SQL Server Login is able to modify data in the database vie the permissions assigned to the Database User.

When you restore a database from one server to another then you are transporting the Database Users within the backup. However, the SQL Server Logins are not transported with the database backup.

You have various options

Option 1: Create a SQL Server Login with the same password
Option 2: Backup the SQL Server Login on the old SQL Server and transport to the new server
Option 3: Use Windows Authenticated Accounts/Groups
Option 4: Use some 3rd party tool
Option 5: Relink the SQL Server Login with the Database User

Drawback of option 1, as you have already noticed, is that the SID of the newly created SQL Server Login will be different than the SID of the SQL Server Login from the old server if the Login is a native (non-Window-AD) login.

When you restore the database backup, the SQL Server instance can not match the SID in the master database, with the SID in the restored database (because that's how it's done) and will not relink the SQL Server Login to the Database User.

However, option 2 (backing up and copying the old SQL Server Login to the new server) will retain the SID of the native SQL Server Login and on restore of the database will automatically relink the SQL Server Login with the Database User.

Additional Info

Database User are stored in <DATABASE_NAME>.sys.database_principals and SQL Server Logins are stored in master.sys.server_principals. You can look at the SID in both tables and verify that the SIDs of the same named SQL Server Logins will vary if you chose option 1.

Option 3. Use Windows Authenticated Accounts / Groups. The SID will not vary, because it is retrieved from Active Directory and will always work after a database restore, if the SQL Server is in the same domain.

Option 4. Use the Export-DbaLogin command from the DBATools.IO Powershell Toolbbox, or some other third-party tool.

Option 5 Manually relink the SQL Server Login with the Database User using the deprecated sp_change_users_login command, or use the ALTER USER ... command like:

ALTER USER database_user WITH LOGIN sql_server_login;

public Role

The public role is a built-in role and can't be assigned directly (explicit) it is assigned automatically, when a SQL Server Login is linked to a Database User:

Every database user belongs to the public database role. When a user hasn't been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Database users cannot be removed from the public role.

^{Reference: Database Level Roles - Public Role (Microsoft | SQL Docs)}

Reference Reading

• SQL Server Logins (Microsoft | SQL Docs)
• Create a Database User (Microsoft | SQL Docs)
• How to Back Up and Restore SQL Server Logins (Microsoft)
• DBA Tools (DBATools.IO)
• Database Level Roles - Public Role (Microsoft | SQL Docs)