SQL Server Group ID | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server Group ID

Is it possible to have SQL Server Group ID like Windows Group Id? CanadaDBA
Hi,
i didn’t understand what you have ask by SQL Server group id like Windows group id but this query will give you username and associatedgroupid ! HTH
quote:Originally posted by FarhadR Is it possible to have SQL Server Group ID like Windows Group Id? CanadaDBA

select l.name,
l.dbname,
l.isntname,
l.loginname,
u.uid,
m.groupuid
from sysusers u
join
syslogins l
on u.sid = l.sid
join
sysmembers m
on m.memberuid = u.uid Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

In Windows we can define Windows users or Windows groups. If you create a Windows group then anyone who is a member of a group has the access rights that you have defined for the group. I want to keep track of each individual. At the moment all the Accounting users have one user ID and all the Sales users have one user ID. I was wondering if I can create Accounting and Sales groups in SQL Server then I can create an ID for each individual and add them to the SQL Server groups. An idea is to create these groups in Windows and grant rights in SQL Server, then create an ID for each individual in Windows and set it as a memeber of that group. Does it work? CanadaDBA
I just received a message from a nice friend that indicates groups in SQL Server is called "roles". Is it true that the roles are in database level. How about if I want a role that has access to both AccDB and InvDB? Can I create a server role? CanadaDBA
True, there are 2 types of roles Fixed server role & Fixed database role.
The first one is applicable on server wide and second one on database wide.
IN order to accomplish your task you can create similar role in 2 databases and grant the user be in both the groups to accomplish the task. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi,<br />you may use those NT logins also , create a role as Satya suggested and then map / assign those logins to created ‘ROLE’ then you don’t need to create seprate SQL LOGINS<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
With Windows authentication you can also map NT groups to database roles, in which case you would not need to add individual users to SQL Server – but of course it depends on your environment whether that would be so wise.
Thanks Satya,
Do you suggest the whole idea to create roles and then grant them to the individuals? Anyone, What is your approach/suggestion? CanadaDBA
Within SQL, the preferred method is to create database roles. Permissions within the database are granted to the database roles. To ‘create’ system users, you grant server login and database access to SQL Server logins and/or Windows logins and/or NT groups. You then assign these users to one or more database roles within each database. You can grant permissions directly to users, but that is not advisable.
For the better security ROLES are best defined to control the permissions.
Most of the enterprise wide network systems we have similar setup, we give db_datareader access to application support users and rest of admin functionality is maintained by us. http://vyaskn.tripod.com/sql_server_security_best_practices.htm for your informaiton. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
My suggestion is to Tag Windows Users/Group to SQL ROLEs if you are working in Domain based Network.
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

To use windows authentication: Create a global group, add the users as members
Create a local group on the SQL server, add the global group as a member
Grant login to the local group (use sp_grantlogin)
Grant the local group access to the database (sp_addrolemember)
Although you will have granted access through groups, your audit logs will contain information on the individual users (be sure to select the appropriate auditing level )
]]>