Creating USER Group in SQL Server 2005

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

kolangara_anand Mr or Mrs. 500 Points: 552 More actions · Answer 1

kolangara_anand

Mr or Mrs. 500

Points: 552

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 · Answer 2

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: 323518 More actions · Answer 3

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: 90309 More actions · Answer 4

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