Solved

Give right  to user in   SQL Server 2005.

Posted on 2009-06-27
4
381 Views
Last Modified: 2012-05-07
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.



0
Comment
Question by:dvplayltd
  • 2
  • 2
4 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24728477
You can ask either your DB Administrator or if you have another login with full privileges, then you can grant read permission on that table to the user which you require.

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
0
 

Author Comment

by:dvplayltd
ID: 24730564
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 ?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24730648
To Kill a SPID, the user Requires the ALTER ANY CONNECTION permission. ALTER ANY CONNECTION is included with membership in the sysadmin or processadmin fixed server roles.

>> Is there way to grant permission only for kill to my user, not all permission for this role ?

Not possible. If you have ALTER ANY CONNECTION permission, it wont be specific for a single user as it applies to all.
0
 

Author Closing Comment

by:dvplayltd
ID: 31597566
OK .. I will add user like mamber of processadmin . Thank you.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
if and else in stored procedure 19 49
SQL Query 2 61
execute a MS SQL script as a schedule SQL job 72 128
create insert script based on records in a table 4 21
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question