January 29, 2013 at 7:28 am
I have sql server 2008 express with so many databases and users created. Each user has different permissions for each databases.Here i find some interesting things
In SSMS in under security->logins i opened properties of admin user. It had all server roles. In user Mapping for some databases admin has only public permission checkbox selected and for some all checkbox are selected.
The database for which only public permission is there in user mapping is able to read,edit and modify.How this possible with only public permission? Is it because of server roles?
I have another doubt.When checking a permission of database for different user should i check it in database properties or in each user properties?(because both has details of permission)
January 29, 2013 at 7:34 am
If an account has SysAdmin fixed server role, they are God and can do what ever they want to do, where ever they want to do it and you cant stop them from doing it.
If it has SysAdmin, then you dont have to give it any other roles, as it will inherit them from the SysAdmin role so granting processadmin is neither here not their as SysAdmin contains all the permissions that processadmin has.
Permissions is a 2 stage approch, you have server permissions which is in the security folder and then database permissions which is in the database level, each show different things, server securable or database securable, so you need to check both to see what a user can and cannot do.
January 29, 2013 at 7:46 am
Thank u.
But i am not clear about two security folder under server and another one under database.In both database role membership is specified.So what is difference between them?
January 29, 2013 at 7:50 am
Because you can specify permissions at the database level on things which are outside of role based permissions
Say I have a role and assign it the ability to execute 99 out of 100 procedures.
All users can only run the 99 procedures
I have 1 super user who is able to run all 100 procedures
So all users and the super user get the role, then the super user gets execute permissons on the remaining procedure.
Now you dont see that the superuser has execute permissions at the server level looking under security->logins, you only see it in the database level under databases->database->users.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply