April 5, 2011 at 6:59 am
I am totally new to SQL server 2008 and am quite confused with user/login/schemes/roles although reading quite a lot of pages...
What is the best and simplest way to create a user/login on SQL server 2008 (access via SQL login) which shall only have the permission insert on one single table in one specific database on the server, no other permissions?
And what is the best way to have a login/user having complete permissions to all tables on a specific SQL database.
There are only to be two users /logins with the described rights on this SQL server (only one database).
Thanks for any hints to a total newbie and sorry for bothering you maybe with such ignorant questions.
April 5, 2011 at 9:01 am
Hi,
You should always control security by adding login (windows or sql auth) to groups. In SQL, we call them Server Roles or Database Roles, depending on the context.
This is not new to 2008, by the way.
http://www.mssqlcity.com/Articles/Adm/SQL70Roles.htm
Server Roles grant you access to the server and server related task. As you describe it, you do not need to put any of your logins into Server Roles.
Database Roles grant you access to the database.
Let's do first the "access to all tables"
You can add your login to db_reader and db_writer.
For the other one, since adding the user to db_writer will grant access to ALL tables on that database, it is not what you want, so you can create a new database role, and set the permissions to that object only (table), and grant insert.
NOTE: INSERT privs does not mean that the user can read the table.
While you can do this using the SSMS GUI, you can also script it like this:
-- create user
USE [master]
GO
CREATE LOGIN [test_user] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- go to your database
USE [AdventureWorks]
GO
-- create the database role
CREATE ROLE [insert_on_one_table] AUTHORIZATION [sa]
GO
-- add the test_user login to the newly created database role
USE [AdventureWorks]
GO
EXEC sp_addrolemember N'insert_on_one_table', N'test_user'
GO
-- configure privs to the newly created database role
use [AdventureWorks]
GO
GRANT INSERT ON [Person].[Address] TO [insert_on_one_table]
GO
MiguelSQL
April 5, 2011 at 9:08 am
Thanks Miguel,
you helped me a lot, I will try this approach. I am totally new to SQL server, by the way :cool:.
Again, thanks for your support, it´s dearly appreciated.
Greetings, Susanne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply