SQL Server 2008

Question

Hello i whould like to know

using only transact sql

how to create a user in a DB and add That user to a Group

In SQL 2000 it was easy to simply add a group and add that user to the group but i m a bit confuse now that i m moving to sql 2008


in SQL 2000 we did

sp_addgroup its the same 
sys


.sp_addrole

and it whould create a new entry in sysusers with issqlrole =1

after that doing
exec db.dbo.sp_adduser user,login,group
creates a new user in a db and it whould be possible to query a user and see gid from that user, it whould represent his group


Now wen we

Exec sys.sp_adduser user,login,group

and that user in sysusers has his gid empty .....

SELECT     sysusers_1.name AS [Group], sysusers.name
FROM         sysusers INNER JOIN
                      sysusers sysusers_1 ON sysusers.gid = sysusers_1.uid
WHERE     (sysusers.issqluser = 1) AND (sysusers.name = 'nuno')  AND (sysusers_1.issqlrole = 1)


 cant be done any more .... in sql 2000 worked how do i do this in sql 2008


how do i query the database to know what Group he belongs?

My objective is to be able to create users and groups, add the users to a determine group and Be able to query that info trough querys

the roles in the DB is to add the privilegs only to the groups and not directly to the users

that info seems a bit hiden on the internet and on sql books online after 3 days serching i dont seem to be able to migrate all my code to work in the same way in SQL2008


 Thank you

Napoleão

Answer 1

In SQL Server 2008:

 

--create role

CREATE ROLE MyRole;

 

--create user (mapped to login of same name)

CREATE USER MyUser;

 

--add user to role

EXEC sp_addrolemember

    @rolename = 'MyRole', 
    @membername = 'MyUser';

 

--query user role memberships

SELECT r.name AS RoleName, u.name AS UserName
FROM sys.database_principals AS u
JOIN sys.database_role_members AS rm ON
    rm.member_principal_id = u.principal_id
JOIN sys.database_principals AS r ON
    r.principal_id = rm.role_principal_id
WHERE
    u.name = 'MyUser';

 

--
Hope this helps.

 

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Answer 2

Thank you