February 23, 2009 at 8:36 am
Hi,
I have a user that appears to have sysadmin access to a server in that when I run the following command as the user I get the permissions that I would expect from a sysadmin:
SELECT * FROM fn_my_permissions(null,'database')
GO
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
CREATE RULE
CREATE DEFAULT
BACKUP DATABASE
BACKUP LOG
CREATE DATABASE
CREATE TYPE
CREATE ASSEMBLY
CREATE XML SCHEMA COLLECTION
CREATE SCHEMA
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CREATE SERVICE
CREATE CONTRACT
CREATE REMOTE SERVICE BINDING
CREATE ROUTE
CREATE QUEUE
CREATE SYMMETRIC KEY
CREATE ASYMMETRIC KEY
CREATE FULLTEXT CATALOG
CREATE CERTIFICATE
CREATE DATABASE DDL EVENT NOTIFICATION
CONNECT
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
ALTER ANY USER
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA
ALTER ANY ASSEMBLY
ALTER ANY DATASPACE
ALTER ANY MESSAGE TYPE
ALTER ANY CONTRACT
ALTER ANY SERVICE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY FULLTEXT CATALOG
ALTER ANY SYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
ALTER
CONTROL
but the following command produces what I would expect from a standard user:
SELECT * FROM fn_my_permissions(null,'server')
GO
CONNECT SQL
VIEW ANY DATABASE
The user can read from any database even though they haven't been granted access to those databases. If I try to create a database or add a linked server as the user, I get permission denied. This is what I would expect but it doesn't explain why the first command returns "CREATE DATABASE" as a permission.
If anyone thinks they can shed some light on this, I would be very grateful!
February 23, 2009 at 8:43 am
I'm going to go with explicitly granted permissions, or explicitly denied permissions. That's the best guess I can come up with.
My first thought was "regular login, granted database owner privileges", but if it extends to more than one database, that may not be true.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2009 at 9:43 am
Is this true of every database? Also check to see if the user has CONTROL DATABASE rights by querying sys.database_permissions. If the permissions are implicit (coming from the server level), you shouldn't see any entries. But if it's explicit (meaning it was granted specifically against the database), then the permissions should show up in the catalog view.
K. Brian Kelley
@kbriankelley
February 24, 2009 at 12:47 am
Thanks for the replies. The problem was that the user's login was mapped to the dbo user. Although the user didn't have an individual login to the server, they were part of a group that had access. The login was mapped to the dbo user as this database had been moved from another server where the user had set it up.
I fixed this using sp_changedbowner.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply