User Permission assignments

[j2willi4]

Is there a way to determine if a privilege granted to a role (for example: PUBLIC) is default to the system, or if it has been explicitly granted by a DBA?

I have seen the isMSshipped property, but haven't found much of a description about it. Does it address the above question?

Thanks!

 

[Catadmin]

I don't know what the isMSShipped property is myself. As far as default privledges for roles, look up Roles in Books Online and it will give you a list of what each Server Role and each Database role can do.

FYI, all roles, fixed or DBA created, have to be manually assigned to users except SA automatically gets SysAdmin access as well as whatever domain user account you specify for the services to use during install.

You can audit changes to logins via Profiler, if that's what you're looking for. You can audit alterations, additions, and drops. I don't know of anything in SQL that actually tracks a history of these changes, though. I honestly don't think SQL keeps a history of changes. Except, perhaps, a CreateDate and UpdateDate (See SysUsers in SQL 2000 and Sys.Database_Principals in SQL 2005).

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

No there isn't a way to see is the right was granted by microsoft or by a dba. Any rights granted by Microsoft will show up as granted by sa.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com