July 6, 2015 at 3:42 pm
I have a 2008R2 server and am trying to peel back the amount of privileges currently enjoyed by the network administrator account.
Currently the network\administrator account is a member of the sys admin role (as used to be the norm in previous versions).
The only task the admin ever need do on this server is to backup one db and restore it to another db via an application interface.
I removed the login from the sys admin role and they could no longer restore the db.
So I looked at user mapping for the account and I am trying to untick the dbs they do not need access to.
Then I am trying to tick the dbs they do need access to.
When I click ok I get the message that the dbo cannot be altered.
I cannot see where the account is mapped to dbo. The dbs in question are either owned by sa or a different domain account.
But when looking at the user mapping it shows dbo as the user and dbo as the default schema (for the admin account).
How can I un-map this, peel the security back to only the backup/restore dbs and not break any applications in the process!
July 6, 2015 at 4:19 pm
This can happen when you restore a database from one server on another. sys.databases says one thing about who is the owner, but sys.database_principals in the database has a different opinion.
The remedy is to change the owner of the database, and change back if desired.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 6, 2015 at 4:25 pm
Thanks, these 2 dbs have always resided on the same server. They are a test and a production of the same application. Bad practice, I know, but not my set-up.
There are 2 other DBs on the server that I am trying to revoke access to for this account. When I untick the access in user mapping and apply I get the error about altering the dbo.
So I run
SELECT SUSER_NAME(principal_id)
FROM sys.database_principals
WHERE name = 'dbo';
On both DBs and both return sa
July 6, 2015 at 4:32 pm
Or course they do. principal_id in sys.database_principals is always 1 for dbo, and 0x01 is also the SID for sa in sys.server_principals. That is, you are mixing apples and oranges.
You should check suser_name(sid) in sys.database_principals. sid is the the common key between sys.database_pricipals and sys.server_principals.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 6, 2015 at 4:33 pm
Here is what I have done and it appears to have achieved the desired result.
On the 2 databases that I wanted this account out of I ran
ALTER AUTHORIZATION ON DATABASE::DBname to sa;
This automatically removed the user mapping without me having to do so.
I then removed the account from the sys admin role and gave it dbo on the 2 databases involved in the back-up/restore process and public within Master.
July 6, 2015 at 4:37 pm
Ah, that's the problem.
You're taking the principal_id of 'dbo' from database_principals, and passing it to the SUSER_NAME function, which will just return the name of the login from server_principals with a principal_id of 1, which will not surprisingly be 'sa'.
The principal_id columns in database_principals and in server_principals aren't related in any way, so that's a bit of a red herring.
This query, which shows which login is the owner of each DB, will show something different, I wager:
SELECT sp.name as OwnerName, d.name as DatabaseName
FROM sys.databases d
INNER JOIN sys.server_principals sp
ON d.owner_sid=sp.sid
Cheers!
EDIT: While I was typing this you posted that you had fixed the issue. The fact that that solution worked means that login was in fact the owner of those DBs. I'm glad you got it resolved!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply