Ken Stoker
kens.programming at verizon.net
Tue Nov 2 23:26:20 CST 2004
Sp_addlogin - creates an account (SQL Server Authentication) at the server level. Sp_adduser - is primarily for backwards compatibility with SQL 7.0. In SS2K, use sp_grantdbaccess. This grants access to a specific database for the supplied account. The account has to be set up at the server level before one can use this. Sp_addgroup - this is also included for backwards compatibility with SQL 7.0. It adds groups (called roles in SS2K) within SQL Server. Sp_addrole - SS2K implementation to create a new SQL Server role/group. Using roles, you can implement permissions to the role instead of to each individual account. This makes managing permissions easier in the respect that there are fewer entities' permissions to maintain. However, users can be in more than one role, and the permissions will be implemented with the conglomeration of all roles' permissions a user is a member of. This part can get quite sticky if not managed correctly. The hierarchy for permissions is Deny --> Grant --> NULL. Let's say a user account is in two roles, role1 and role2, with the following permissions on the same table or view: ROLE1 ROLE2 SELECT GRANT NULL UPDATE DENY GRANT INSERT NULL GRANT DELETE DENY GRANT In this case, as I understand it, the account can SELECT because Role1 Grants it, superceding Role2 which implements nothing here. UPDATE, which is Granted in Role2, is actually Denied because of the implementation in Role1. INSERT would be Granted because Role2's Grant overrides Role1's non-implementation. And DELETE is Denied because Role1's Deny rights overrides Role2's Grant rights. Sp_grantlogin is used to create Windows Authentication accounts within SQL Server at the server level. Then you would use sp_grantdbaccess to give that account access to a specific database within the SQL Server instance. There is a lot of information on these in BOL under System Stored Procedures. If you scroll down, you will find a section for Security Procedures. Again, this is the way I have come to understand it. If I am wrong, I know someone here will correct me. Ken -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. Colby Sent: Tuesday, November 02, 2004 8:43 PM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] Login names vs users What is the relationship between login names and users, groups and roles? SP_AddLogin SP_AddUser Sp_AddGroup Sp_AddRole John W. Colby www.ColbyConsulting.com Contribute your unused CPU cycles to a good cause: http://folding.stanford.edu/ _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com