Creating USER Group in SQL Server 2005

  • 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

  • Hi anybody able to help me out on this?

    Rgds,

    Anand.

  • Hi Friends,

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

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

  • 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!

  • 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

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

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