Problem performing remote queries

[rhaazy]

I am trying to a simple insert statement from a remote application
against a sql server 2005 database. To fix the problem I was having,
I had to grant the Login I was using the role of sysadmin. However I
don't want this user to have that kind of control, what would be the
best role to allow the user full access(includin g remoting) to only
one particular database?

[Marcin A. Guzowski]

Re: Problem performing remote queries

rhaazy pisze:

I am trying to a simple insert statement from a remote application
against a sql server 2005 database. To fix the problem I was having,
I had to grant the Login I was using the role of sysadmin. However I
don't want this user to have that kind of control, what would be the
best role to allow the user full access(includin g remoting) to only
one particular database?

First, tell us what the problem was (provide an error message or other
details).

You can configure detailed permissions in SQL Server 2005, granting
sysadmin server role is far too much. Grant the login only enumerated
permissions that are essential to perform certain tasks (simple insert
in your case). Here is an T-SQL statement to do it:

GRANT INSERT ON your_table TO some_user;

Of course there are alternate solutions - e.g. database role
(db_datawriter) , but try that one I mentioned above.


--
Best regards,
Marcin Guzowski

http://guzowski.info

[rhaazy]

Re: Problem performing remote queries

The error message is simply that my user didn't have permission to
execute the statement.

I would like to be able to grant the user insert, update, delete,
select on all tables in a particular database.(remot ely or local, both
situations are possible)

How would I do this?

[Marcin A. Guzowski]

Re: Problem performing remote queries

rhaazy pisze:

The error message is simply that my user didn't have permission to
execute the statement.
>
I would like to be able to grant the user insert, update, delete,
select on all tables in a particular database.(remot ely or local, both
situations are possible)
>
How would I do this?

Permissions are assigned to logins/users and there is no difference
between remote and local scenario.

If you want all DML operations granted on all tables in particular
database, simply grant two database roles to your database user:

USE your_database;
GO
EXEC sp_addrolemembe r N'db_datareader ', N'database_user ';
GO
EXEC sp_addrolemembe r N'db_datawriter ', N'database_user ';
GO



--
Best regards,
Marcin Guzowski

http://guzowski.info

[rhaazy]

Re: Problem performing remote queries

As it turns out the db_owner is a more likely canidate for the level
of power I wish to give the user.

So what I need to do is add to my database install script, after I add
the user to the database, i need to grant Database Role Membership
(db_owner) for the database ClientScan for the user CSAdmin

exec sp_addlogin 'CSAdmin', 'pwd'

USE ClientScan
exec sp_adduser 'CSAdmin'

exec sp_addrolemembe r db_owner, CSAdmin

If there is anything wrong with my syntax please correct it.

[Erland Sommarskog]

Re: Problem performing remote queries

rhaazy (rhaazy@gmail.c om) writes:

As it turns out the db_owner is a more likely canidate for the level
of power I wish to give the user.
>
So what I need to do is add to my database install script, after I add
the user to the database, i need to grant Database Role Membership
(db_owner) for the database ClientScan for the user CSAdmin
>
exec sp_addlogin 'CSAdmin', 'pwd'
>
USE ClientScan
exec sp_adduser 'CSAdmin'
>
exec sp_addrolemembe r db_owner, CSAdmin
>
If there is anything wrong with my syntax please correct it.

Since you are on SQL 2005, I would suggest that you use CREATE LOGIN
and CREATE USER rather than sp_adduser and sp_addlogin.

Note that there is a difference between CREATE USER and sp_adduser: the
latter will create a schema called CSAdmin and make that the default
schema for CSAdmin. If you only use CREATE USER, CSAdmin's default schema
will be dbo, and no schema CSAdmin will be created.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx