December 11, 2003 at 4:46 pm
I'm sure this should go in the TSQL postings, but it kind of has to do with security too.
I want to add a user to a database using TSQL. I then want to give that user rights to read from all tables in this database using SELECT. I want no other rights to this user other than to log in, read data, and disconnect.
anyone have a TSQL procedure that can help me out here? I can add the users and put them into roles, but giving them select rights to all tables in a database requires me to do a grant to each table individually apparently.
Any ideas would be very much appreciated.
December 11, 2003 at 5:34 pm
Add them to the db role "datareader". This can be done with T-SQL
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 12, 2003 at 1:47 am
Assuming that you have set up your logins set up then to add them into the database you would use:
exec sp_grantdbaccess 'the login','the username in the db'
exec sp_addrolemember 'db_datareader','the username in the db'
The sp_grantdbaccess stored procedure supercedes the old sp_adduser stored procedure. sp_adduser was like sp_grantdbaccess except that it had a 3rd parameter which was a group/role name.
If you haven't set up your logins then use sp_addlogin to create SQL Server logins or sp_grantlogin to allow NT logins.
He was not wholly unware of the potential lack of insignificance.
December 12, 2003 at 7:43 am
To assign someone to an existing role, you could use the following SQL...
GRANT SELECT ON
tablename
TO rolename
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply