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.
|
1 2 3 4 5 |
use [database_name] GRANT SELECT ON SCHEMA::[dbo] TO [DOMAIN\user] GO GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [DOMAIN\user] GO |
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:
|
1 |
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:
|
1 2 3 4 5 6 7 |
SELECT Name, CASE WHEN isntname = 1 then 'YES' WHEN isntname = 0 then 'NO' END AS 'Windows Login' FROM SYSLOGINS 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE #sp_who2 ( SPID INT, 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, SPID2 INT, REQUESTID INT ) INSERT INTO #sp_who2 EXEC sp_who2 SELECT * FROM #sp_who2 --WHERE DBName = 'databasename' DROP TABLE #sp_who2 |