June 13, 2011 at 2:21 am
Hello folks, I am looking for some clarifications to clear few doubts on SQL Security.
1. Difference between db_datareader/writer and db_datadenyreader/writer. If a user has granted both, what permission level will be in effect ?
2. If a Windows group is created, then should it be created for all the domains available or will it be one for all domains [like asia\groupread, emea\groupread or just groupread ?]
3. I'm testing by creating a group and giving db_ddladmin, db_ddlreader and db_ddlwriter permissions. However can't execute stored procedures. Isn't it included in any of the above roles ?
June 13, 2011 at 3:07 am
One more thing:
4. How is service account different from normal login [of course apart from their functionalities like Service Accoutn should be used only by application and Login is for general users]. Is there a different way these are created.
June 13, 2011 at 3:28 am
1. I don't know. You could find out for yourself by creating a user and making it a member of both roles.
2. Not sure what you mean here. If you have two different Windows groups, that's two different logins on SQL Server, unless you create a single Windows group that contains both groups.
3. No. Grant EXECUTE on stored procedures indivdually, or on the schema that they are in.
4. Do you mean application service accounts, or the service accounts for SQL Server and SQL Server Agent?
John
June 13, 2011 at 4:03 am
sqlnaive (6/13/2011)
1. Difference between db_datareader/writer and db_datadenyreader/writer. If a user has granted both, what permission level will be in effect ?
The first Grants the permissions. The second Denies the permissions. Have a read through Books Online for how GRANT, REVOKE and DENY interact.
3. I'm testing by creating a group and giving db_ddladmin, db_ddlreader and db_ddlwriter permissions. However can't execute stored procedures. Isn't it included in any of the above roles ?
No.
Create a custom database role, grant it permission to execute the procedures and then give users that role.
GRANT EXECUTE ON schema::<schema name> TO <Custom Role Name>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 4:41 am
Read this :-
http://www.mssqlcity.com/Articles/Adm/manage_users_permissions.htm
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 13, 2011 at 4:47 am
Point 2: Usually organizations have different Domains for different regions. So if we have to create any NT group to get people access on SQL box through Windows NT authentication, should it be created among all the domains or in NT, group should be created just once [globally]
Point 4: I mean application service account.
June 13, 2011 at 4:53 am
2. I'm still not sure what you mean. If my original reply didn't answer your question, please say what you don't understand or what further information you need.
4. A login for an application service account should be granted the permissions that it needs and only those permissions. It is created in the same way as any other login, although sometimes the installation of the application will automatically create the login. If this is the case, you should verify that a lazy application developer hasn't given it greater access than it needs, such as sysadmin.
John
June 15, 2011 at 8:27 pm
Thanks John. One more doubt. Can we only use Mixed mode authentication for non windows user/applications ?
June 17, 2011 at 2:19 am
Yes. If a user doesn't have a Windows login, they'll need a SQL login instead, and you'd need to put the server in mixed authentication mode.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply