Tag Archives: Users

Granting View Definition to All Procedures

The other day I came across something where I needed to grant view definition rights to a specific user for one single DB. Now, there are several different ways to achieve this but I decided to dabble in just writing some code to take care of it for me. Others might have something very similar or different mechanisms to do such but for me I used the following code:

The basic gist is as follows:

  1. Create Temp Table – you can use a # table just drop it when done with it
  2. Declaration of Variables
  3. Insert data into temp table – only want to grab the procedures and nothing with a _dt
  4. Grab all of the procedures found in the DB and dynamically set the grant permissions to a SQL string
  5. Execute the SQL string
  6. Restart the counter and reset the name variable just for good measure
  7. Select the temp table to view what was just granted view definition to

Code in doing this:

DECLARE @PermTable TABLE
(
PK_ID INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED ,
[name] SYSNAME
)

–declare variables
DECLARE @name SYSNAME ,
@RowCount INT ,
@RecordCount INT ,
@strSQL VARCHAR(2000)

INSERT INTO @PermTable
(
[name]
)

SELECT ROUTINE_SCHEMA + ‘.’ + ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘Procedure’
AND ROUTINE_NAME NOT LIKE ‘dt_%’

— counters for while
SET @RecordCount = ( SELECT COUNT(*)
FROM @PermTable
)

SET @RowCount = 1
WHILE ( @RowCount < @RecordCount + 1 )
BEGIN
SELECT @name = [name]
FROM @PermTable
WHERE PK_ID = @RowCount
SET @strSQL = N’Grant VIEW Definition on ‘
+ RTRIM(CAST(@name AS VARCHAR(128))) + ‘ to [User]’

–Execute the Sql
EXEC(@strSQL)

–Restart the Counter
SET @RowCount = @RowCount + 1

–Never know so why not reset
SET @name = NULL
END

SELECT *
FROM @PermTable

Hope this helps; cheers.

 

Advertisements

Someone Forget to Logoff that Remote Desktop Session?

 

Did Someone Forget To Logoff?

Phone rings….. alerts start going off……… coffee hasn’t been made yet….. it’s 2:13 a.m. and the issue requires you to remote login to the server to perform some task; only to find out that the server has already exceeded the maximum number of users. Has that happened to you before? Has me; either other people are on the box looking at something else or someone forgets to go ahead and log off when they are done using the box.

When this occurs I like to use the QWINSTA\RWINSTA operations from the command line to help me identify who is on the box and then graciously log them off. I know there are other methods to complete the same task; however I tend to roll with this one as it is engrained in my head.

To display information about Terminal Sessions you can type the following command in the cmd prompt:

  • qwinsta /server:[servername]

This command will give you the session name, username, id, state, type, and device.

This information is good however; how can you get someone off the server? That can be accomplished by using a similiar cmd line; howerver I DO RECOMMEND that you check with the individual prior to just booting them out unless it is an extreme emergency

  • rwinsta /server:[servername] [sessionid]

You need to make sure that you capture the sessionid that you saw from the qwinsta function in order to log them off.

I know Windows Administrators go through the Terminal Services Manager route but if you’re like me and enjoy some good command line based tools this can be another option for you; especially when you are woken up at night and the other people logged in could be fast asleep!!