Creating USER Group in SQL Server 2005

• kolangara_anand

  Mr or Mrs. 500

  Points: 552

  More actions

  July 22, 2009 at 10:07 am

  #136589

  Hi,

  I have created a user group in my computer. I want to now associate these users to a particular db. I want these users to be authenticated by Windows. To achevie this I first created the User group SQL_users on my computer and did the rest as below.

  [USE MASTER]

  CREATE LOGIN [MyComp\SQL_users]

  FROM WINDOWS

  WITH DEFAULT_DATABASE = TEST

  [USE TEST]

  CREATE USER SQL_users FOR LOGIN [MyComp\SQL_users] ;

  GO

  grant CREATE FUNCTION to [SQL_users]

  grant CREATE PROCEDURE to [SQL_users]

  grant DELETE to [SQL_users]

  grant EXECUTE to [SQL_users]

  grant INSERT to [SQL_users]

  grant UPDATE to [SQL_users]

  grant SELECT to [SQL_users]

  GRANT CONNECT to [SQL_users]

  go

  However even with this the users in the group were not able to connect to the database.

  Please note I have allowed mixed mode authentication for this server. Also in the SQL Surface Area configuration I have enabled the option 'Local and Remote Connections' Using TCP/IP only.

  However I am still running into the ubiquitous 18456 Error Message.

  Is there something still which I have to do before the members in the Windows Group SQL_users can login to the DB.

  Regards,

  Anand

• kolangara_anand

  Mr or Mrs. 500

  Points: 552

  More actions

  July 22, 2009 at 10:18 pm

  #1028753

  Hi anybody able to help me out on this?

  Rgds,

  Anand.

• mohd.sql

  Old Hand

  Points: 384

  More actions

  July 25, 2010 at 2:45 pm

  #1197969

  Hi Friends,

  I am unable to understand what exactly SQL Server group is ?????

  can anyone explain and steps to create it....????

• Lowell

  SSC Guru

  Points: 323522

  More actions

  July 25, 2010 at 4:05 pm

  #1197975

  what the original poster calls a "group" is what books on line calls a "role". you typically create a role and add the expected rights tot eh role, then add users to the role.

  here's a nice script i like to use as an example:

  create database Whatever

  GO

  USE Whatever

  CREATE ROLE [ReallyReadOnly]

  --give reader rights to this group

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]

  --explicitly DENY access to writing

  ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]

  GRANT EXECUTE TO [ReallyReadOnly]

  --create the Role for my Dev guys, who create tables and procs

  CREATE ROLE [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

  GRANT EXECUTE,ALTER TO [WhateverDEVAdmins]

  --create role for my normal users

  CREATE ROLE [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]

  GRANT EXECUTE TO [WhateverDEVUsers]

  --now add specific users to nearly-Admins

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  END

  USE [WHATEVER]

  --make a user in the db for the matching login

  CREATE USER [bob] FOR LOGIN [bob]

  CREATE USER [jeff] FOR LOGIN [jeff]

  --add these logs to the role

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

  CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

  CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]

  EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeffrey Williams

  SSC Guru

  Points: 90348

  More actions

  July 25, 2010 at 6:49 pm

  #1197983

  Are your users logging into that machine - or are they logging into their own machines and trying to connect? If they are logging into their own machines, are they authenticating using a domain account or local account?

  For this to work, they would need to login to their machines with a domain account - and you would have to add their domain logins to the local group on that machine.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs