zhshqzyc
asked on
Security level
Hello, I have a bunch of stored procedures in a database.
Can we make a part of them available to a role "public", the rest of them are available to "sysadmin" ?
Can we make a part of them available to a role "public", the rest of them are available to "sysadmin" ?
Alfredo Luis Torres Serrano
Sure through the securables at the role properties.
ASKER
Then how to implement it?
You mind with a script or query????
Manually you have to go to Database/Security/Roles
Right click on the selected role
Go to properties
Securables and add the desired Store or group os stores and select the permission decided.
Manually you have to go to Database/Security/Roles
Right click on the selected role
Go to properties
Securables and add the desired Store or group os stores and select the permission decided.
ASKER
I am assigned as "public" and "sysadmin". But "sysadmin" doesn't show up. Why?
You can't prevent SYSADMIN from seying the stored procedures in a SQL server unless they are encrypted. That is because "Members of the sysadmin fixed server role can perform any activity in the server."
http://msdn.microsoft.com/en-us/library/ms178032(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms178032(SQL.90).aspx
ASKER
I think that I misunderstood the concept between "Database Role" and "Server Role".
>>I think that I misunderstood the concept between "Database Role" and "Server Role".<<
You do realize that Public is a database role and sysadmin is a server role, right?
You do realize that Public is a database role and sysadmin is a server role, right?
Ah, never mind, I see that you have indeed realized that.
ASKER
But I find "public" exists in both database role and server role.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.