dvplayltd
asked on
Give right to user in SQL Server 2005.
Hi experts !
Im using SQL Server 2005 SP3. Im log with limit user via SQL Server autentaction. But now, I want to execute this :
Select P.spid, p.Hostname, P.Program_Name, P.Loginame, C.client_net_address, c.client_tcp_port
From master.dbo.sysprocesses P Left Join sys.dm_exec_connections C on P.spid = c.session_id
And probably will want to be able to do kill spid. Other question: how is exatly synatcis of kill spis ?
The main question is: is there way to give right only to these master.dbo.sysprocesses, sys.dm_exec_connections and kill spid ? I do not want to give more right that is nesseasry to this user & with give him role . I need SQL script that do this.
Im using SQL Server 2005 SP3. Im log with limit user via SQL Server autentaction. But now, I want to execute this :
Select P.spid, p.Hostname, P.Program_Name, P.Loginame, C.client_net_address, c.client_tcp_port
From master.dbo.sysprocesses P Left Join sys.dm_exec_connections C on P.spid = c.session_id
And probably will want to be able to do kill spid. Other question: how is exatly synatcis of kill spis ?
The main question is: is there way to give right only to these master.dbo.sysprocesses, sys.dm_exec_connections and kill spid ? I do not want to give more right that is nesseasry to this user & with give him role . I need SQL script that do this.
ASKER
To rrjegan17
Thank you, I will give you point. Only 1 question - for rights of Kill
I found this:
Permissions
Requires membership in the sysadmin and processadmin fixed database roles.
Is there way to grant permission only for kill to my user, not all permission for this role ?
Thank you, I will give you point. Only 1 question - for rights of Kill
I found this:
Permissions
Requires membership in the sysadmin and processadmin fixed database roles.
Is there way to grant permission only for kill to my user, not all permission for this role ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK .. I will add user like mamber of processadmin . Thank you.
grant select on master.dbo.sysprocesses to ur_user;
ur_user needs to be replaced with your user login and the above statement needs to be executed with a user who has privileges on that table. ( This should help and I am not able to test since I dont have access to machine)
Syntax to kill spid:
kill spid
replace spid with the spid you need to kill.
Hope this helps