June 4, 2009 at 6:05 am
They have laid-off the only person doing the DBA work! Now I am trying to do some things
that I know I should not. Have a new customer and converting their ACCESS application
to SQL-Server 2005, creating tables, stored procedures, etc is no problem as I am a developer
but when it comes to setting up database permissions I am totally lost.
I am currently using sa for everything but it will not go to production like that. I have goggled
and found some and then others that were different, ways of performing that its.
I want to create a user that can use in my .NET connection string to read, write, update, delete
from all tables within the particular database, no dropping tables, etc though!
And links, tips would be appreciated and sorry for such a rookie question.
Thanks!
June 4, 2009 at 6:27 am
Books OnLine will be your best resource here.
search on these keywords:
authentication modes
create login
create user
fixed database roles
fixed server roles
sp_addrolemember
grant
USe a windows authenticated ID if you can. SQL authenticated ids have improved greatly in SQL 2005 but windows authenticated still most secure and easier to work with.
You will need to create the login to give the id access to SQL itself. then in the database use create user to give the id access to the database.
To give the id permissions in the database add to roles or use the grant command. To get the permissions you describe the id could be added to the fixed database roles db_datareader and db_datawriter. However this gives the id direct access to the tables which is not the most secure. If you are writing stored procedures to manipulate the data give the id exec permissions on the relevant stored procs, then the user will only be able to perform the functions as coded in the stored proc.
If there will be multiple users, create a windows group in AD, add all the users to that group and then do all the above for that group and all users will inherit the same permissions.
---------------------------------------------------------------------
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply