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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy