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(including remoting) to only
one particular database?
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(including 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
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.(remotely or local, both
situations are possible)
How would I do this?
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.(remotely 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_addrolemember N'db_datareader', N'database_user';
GO
EXEC sp_addrolemember N'db_datawriter', N'database_user';
GO
--
Best regards,
Marcin Guzowski
http://guzowski.info
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_addrolemember db_owner, CSAdmin
If there is anything wrong with my syntax please correct it.
rhaazy (rhaazy@gmail.com) 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_addrolemember 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@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx