DB_Owner rights without Db_Owner role

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

DB_Owner rights without Db_Owner role

Author

Topic

joeller
Starting Member

15 Posts

Posted - 2011-09-22 : 12:05:27

Very strange thing happened. We have a database that exists on a test server and on a production server. There is a user on that database mapped to a SQL Login. When this login was added to the database it was assigned the default schema of "dbo" (However it was not mapped to the "dbo") user. This user is a member of the db_datareader and db_datawriter roles. I ran the query

execute as user = 'UserName'

SELECT 'UserName' as [user], permission_name

FROM fn_my_permissions(NULL, 'Database')

WHERE Permission_name not in ('connect', 'delete', 'Insert', 'update', 'select')

ORDER BY subentity_name, permission_name;

REVERT;

On the database on the test server it returned no rows as expected. However on the production server, it returned the same list of permissions as if it were a member of the db_owner role. Yet a careful exam definitely showed the user as not being a member of the db_owner role. Knowing the people involved in creating the database I am sure they did not explicitly assign each of those 55 permissions to that user.

I am baffled as to how those permissions were ever assigned. Is it possible that the user could be granted theses rights merely from being given a default schema of "dbo"? Is it possible for a user to be assigned to the db_owner role and still have these permissions show up after being removed from that role? Both these ideas seem highly unlikely, but as Lazarus Long said, "There is nothing as unllikely as a baby." So stranger things have been known to happen. Is it possible that the function "fn_my_permissions(NULL, 'Database')" is showing all permission whether they are explicitly granted or denied and that these are "denied"?

I of course have run queries against the sys.database_permissions table. The only results I got back showed this user with only connect rights.

Does anyone have any ideas on how this could have come about? If so I would like to hear from you.

E.R. Joell MCDBA

joeller
Starting Member

15 Posts

Posted - 2011-09-22 : 12:10:10

As an addedum;
I ran the following query as suggested by someone:
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];

As soon as I saw the table database_permissions I was pretty sure it would only show the explicitly granted permissions and no permissions that are dervied from roles. my result showed the user as having only "Grant Connect" explicit permissions on the database. Whereas the fn_my_permissions function with the above stated where clause shows the user as having the following explicit and derived permissions in production.

ALTER
ALTER ANY APPLICATION ROLE
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROLE
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
ALTER ANY USER
AUTHENTICATE
BACKUP DATABASE
BACKUP LOG
CHECKPOINT
CONNECT REPLICATION
CONTROL
CREATE AGGREGATE
CREATE ASSEMBLY
CREATE ASYMMETRIC KEY
CREATE CERTIFICATE
CREATE CONTRACT
CREATE DATABASE DDL EVENT NOTIFICATION
CREATE DEFAULT
CREATE FULLTEXT CATALOG
CREATE FUNCTION
CREATE MESSAGE TYPE
CREATE PROCEDURE
CREATE QUEUE
CREATE REMOTE SERVICE BINDING
CREATE ROLE
CREATE ROUTE
CREATE RULE
CREATE SCHEMA
CREATE SERVICE
CREATE SYMMETRIC KEY
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
EXECUTE
REFERENCES
SHOWPLAN
SUBSCRIBE QUERY NOTIFICATIONS
TAKE OWNERSHIP
VIEW DATABASE STATE
VIEW DEFINITION

But it has none of these in Test. (in fact the query cited in the original post shows an empty dataset.) Yet the user is configured identically in both database, In fact, both of the databases, are the results of a restoration of a backup of the same database.

So my question remains how can that user be shown as having all of those permissions without being a member of the role (db_owner) that grants those rights, and not having any explicitly granted rights?

E.R. Joell MCDBA

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 12:35:09

Checked that user is not member of sysadmin server role?

joeller
Starting Member

15 Posts

Posted - 2011-09-22 : 12:45:00

I have been able to verify that in the recent past, the user was in the db_owner role. Is it possible this link http://support.microsoft.com/kb/168001 has anything to what I am oserving?

Update: And now, automagically, this user who was not shown as being a member of the db_owner role on production, is suddenly is in db_owner. And when db_owner is unchecked, those spurious rights go away!!!! What the frack is going on? Given the subject of the above link is it possible that restored database was forced to change its settings to reflect those for that user as listed in master? If so why didn't it do that earlier?

E.R. Joell MCDBA

joeller
Starting Member

15 Posts

Posted - 2011-09-22 : 14:14:54

quote:
Originally posted by russell

Checked that user is not member of sysadmin server role?

No the user was never in the sysadmin role. (In fact, on this server no SQL Server Logins are members of the sysadmin server role except sa and it is disabled.)

E.R. Joell MCDBA

joeller
Starting Member

15 Posts

Posted - 2011-09-26 : 12:42:01

Now things get really weird.

On one of the databases against which we are running the fn_mypermissions query we are getting results like so. (Remember the query filters out connect, insert, delete, select and update).

testapl BACKUP DATABASE
testapl BACKUP LOG
testapl CREATE DEFAULT
testapl CREATE PROCEDURE
testapl CREATE RULE
testapl CREATE TABLE
testapl CREATE VIEW

users roles show the only role this user is a member of is "db_datareader". Database permissions show the only explicit permission that this user has been granted is "connect" This result shows up for every user in this database. this is the only database where these permissions show up in the query results at all.

I ran the following Revoke script.

Revoke BACKUP DATABASE from testapl
Revoke BACKUP LOG from testapl
Revoke CREATE DEFAULT from testapl
Revoke CREATE PROCEDURE from testapl
Revoke CREATE RULE from testapl
Revoke CREATE TABLE from testapl
Revoke CREATE VIEW from testapl

Then I ran the fn_my_permissions query again. No change in result. Per http://msdn.microsoft.com/en-us/library/ms177573.aspx All of these Create pemissions are implied by the database permission "Alter", and the backup permissions are implied by "control" so I also ran

Revoke Control from testapl
Revoke Alter from testapl

Running the fn_my_permissions query resulted in no change. I then used the GUI to set this user to have Control and Alter explicitly. I ran the fn_my_permissions query again and this time saw 55 implied permissions set. I used the GUI to undo those permissions and the original 7 permissions still showed.

I then opened Database permissions window for that user and set each of these rights manually. I ran the query again. No Change. I then ran the revoke script. I then checked the database permissions window and all the checkboxes marking these permissions were unchecked. But when I ran the fn_my_permissions query again it still showed the same seven permissions.

Finally in desparation I opened database permissions and explicitly checked "Deny" for each of these permissions for this user. Finally my query returned no findings for this user. When I removed the explict "DENY" for each of these permissions for this users then the fn_my_permissions query again went back to showing these seven permissions. My question then is

Why do I have to set these permissions to be explicitly denied for each user on this and only this database in order for these users to not have these rights? Where are these rights being set such that they disregard any instructions I apply to revoke them?

E.R. Joell MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources