September 1, 2011 at 9:07 am
hi all,
i've created an sqladmin group in my domain controller which consists of 5 users. My problem is what privileges shall i've to assign to this group so that i can restrict them without having full administrative privileges advantage, but sufficient enough to do any sql server task incluing xp_sendmail or adhoc queries
please specify the privileges that i've to assign,
September 1, 2011 at 11:05 am
As per BOL xp_sendmail "Requires membership in the sysadmin fixed server role, but EXECUTE permissions can be granted to other users. However, for security reasons, we recommend that permissions for this stored procedure be limited to members of the sysadmin fixed server role.". For running Ad-hoc queries, what kind of queries? Updates, inserts and deletes as well? If not, you can just give data_reader rights.
-Roy
September 1, 2011 at 11:26 am
No i am asking about the permissions or privileges that i've to configure in windows operating system to create the dedicated sqladmin group for service acccounts to run, not on the sql server roles and permissions.
September 1, 2011 at 11:40 am
They can just be normal users with no high privilege. For example the service account should be a least privileged user in the domain but that user is a SYSAdmin on the SQL Server.
-Roy
September 2, 2011 at 1:01 am
Hi,
Can we give database_role, db_owner for normal sqlserver user and application user?
Please tell me, default database owner and schema "dbo", could assign normal and application user?
How to assign least premission for create, insert, update, delete, execute, create function, and create view to default database owner and schema "dbo" and normal sql user?
I have create one sqluser for accessing and devlopment team. but I could not able to assign those permission to sql user.
I tried.
1. create sqluser(testuser) with defualt puplic role
2. testuser assign to default schema on 'dbo'
3. grant permission to testuser for create, insert, update, delete, execute, create function, and create view.
4. after connect ssms by using testuser- could not able to create table and all. so how to assign those permission this testuser.
Thanks
September 2, 2011 at 1:12 pm
ananda.murugesan (9/2/2011)
Hi,Can we give database_role, db_owner for normal sqlserver user and application user?
Please tell me, default database owner and schema "dbo", could assign normal and application user?
How to assign least premission for create, insert, update, delete, execute, create function, and create view to default database owner and schema "dbo" and normal sql user?
I have create one sqluser for accessing and devlopment team. but I could not able to assign those permission to sql user.
I tried.
1. create sqluser(testuser) with defualt puplic role
2. testuser assign to default schema on 'dbo'
3. grant permission to testuser for create, insert, update, delete, execute, create function, and create view.
4. after connect ssms by using testuser- could not able to create table and all. so how to assign those permission this testuser.
Thanks
you have to assign the testuser to a database role either db_datareader or db_datawriter or db_owner whatever that user is allowed to do. public role is the default database role which does nothing. on top of these roles u have to explicitely grant permissions on the database objects
or
other way is to create ur own custom database role with required privileges and simple add members to that role instead of adding to the public role
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply