Define Groups Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Define Groups Script

Author

Topic

Jose Miguel
Starting Member

3 Posts

Posted - 2007-04-24 : 16:11:07

Hello,

I was wondering if someone could point me in the right direction here. I'm fairly new to SQL Server so please excuse my lack of expertise. I want to create a script that will assign roles to users based on the fixed database roles SQL Server has ( db_datareader, db_datawriter, etc). However, I only want to assign these roles to user tables. Here's what I have so far:

USE Databasename
GO
EXEC sp_grantdbaccess 'DOMAINNAME\USERID', 'USERID'
GO
EXEC sp_addrolemember 'db_datareader', 'USERID'
GO
EXEC sp_addrolemember 'db_datawriter', 'USERID'
GO

Please help!

Jose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-24 : 16:22:47

That is not possible with fixed database roles. To grant read/write on a specific table, you would use GRANT statement. Just create a user role, add your users to that, then do this:

GRANT ... ON YourObject TO YourRole

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources