Background: I had a user that needed to be able to read, but not execute, stored procedures in the DBO schema. I used these methods below to achieve this task.
There are two methods to granting schema permissions in SQL server.
- One is through T-SQL commands:
GRANT SELECT ON SCHEMA::[dbo] TO [DOMAIN\user]
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DOMAIN\user]
I’ve designed an integration job that deletes all data from a table and imports new records each night. The table has an identity column in it, so the value (or seed) will always be growing.
Here is the easiest way to reseed the identity column for a table:
DBCC CHECKIDENT (tablename, reseed, 0)
This will force the next inserted identity value to be 1 (0+1=1 ).
Here is a simple script that checks for sysadmins on an instance:
WHEN isntname = 1 then 'YES'
WHEN isntname = 0 then 'NO'
END AS 'Windows Login'
WHERE sysadmin = 1 and hasaccess = 1
Here is a simple script that allows you to sort and search for items in the sp_who2 stored procedure. Works with SQL 2005 and up:
CREATE TABLE #sp_who2 (
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
INSERT INTO #sp_who2 EXEC sp_who2
--WHERE DBName = 'databasename'
DROP TABLE #sp_who2