View Permissions in SQL Server

Step I: List all dbo Grantor Permissions

EXEC sp_helprotect NULL, NULL, dbo
EXEC sp_helprotect @grantorname = 'dbo'

Step II: Listing the permissions for a table

EXEC sp_helprotect 't_register'


Step III: Listing the permissions granted by a specific user

EXEC sp_helprotect NULL, 'Linda'

Step IV: Listing the statement permissions only

EXEC sp_helprotect NULL, NULL, NULL, 's'

Log Truncate for SQL Server Database

Objective: Truncate log in the SQL Server Database to provide more space for the Server:

Please use the below procedure to get script to apply for Truncating the Log in the SQL Server.

CREATE procedure P_LogTruncate
AS
Begin
DECLARE @DBNAME varchar(50)
DECLARE @LOGICALFILENAME varchar(50)
DECLARE CURSOR_LOGDB CURSOR for select db_name(database_id) as DBNAME,NAME AS LOGICALFILENAME from sys.master_files(NOLOCK) WHERE TYPE_DESC='LOG'
OPEN CURSOR_LOGDB
FETCH NEXT FROM CURSOR_LOGDB INTO @DBNAME, @LOGICALFILENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @SQL1 Varchar(MAX)
Declare @SQL2 Varchar(MAX)
SET @SQL1 = 'BACKUP LOG '+@DBNAME+' WITH TRUNCATE_ONLY'
PRINT @SQL1
--EXEC (@SQL1)

SET @SQL2 = 'use ' + @Dbname + ' DBCC SHRINKFILE(' + @LOGICALFILENAME + ',0)'
PRINT @SQL2
--EXEC (@SQL2)

FETCH NEXT FROM CURSOR_LOGDB INTO @Dbname, @LOGICALFILENAME
END
close CURSOR_LOGDB
Deallocate CURSOR_LOGDB
END

Change compatibility level of SQL Server Database

Objective: Change compatibility level of database

Step I: Change database to single user before changing the db compatibility

ALTER DATABASE [DBNAME] SET MULTI_USER WITH NO_WAIT

----

ALTER DATABASE DBNAME SET SINGLE_USER WITH NO_WAIT

------

EXEC sp_dboption 'DBNAME', 'single user', 'true'

GO

Step II: Kill all spids for that database except your spid

Select spid from master..sysprocesses where dbid = db_id('DBNAME') and spid <> @@spid

--------

Kill those spids using kill command:

For eg: kill 94

--------

Or you can close all connections using the below script:

/**

DECLARE @DatabaseName nvarchar(50)

DECLARE @SPId int

SET @DatabaseName = N'DBNAME'

DECLARE my_cursor CURSOR FAST_FORWARD FOR

SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DBNAME) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0

BEGIN

KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId

END

CLOSE my_cursor

DEALLOCATE my_cursor

********/

Step III Change compatibility level:

EXEC sp_dbcmptlevel AdventureWorks, 80;

GO

----SQL Server 2000 database compatible level to SQL Server 2005

EXEC sp_dbcmptlevel AdventureWorks, 90;

GO

Step IV: Change single user to multi user:

EXEC sp_dboption 'c_data1dk', 'single user', 'false'

Grant Permissions to All Stored Procedures

Objective: To grant permissions for specific user to all stored procedures in the specific Database.

use dbname
go
CREATE PROCEDURE P_GrantExectoAllSP @user sysname
AS
SET NOCOUNT ON

--Step I: Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)

--Step 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)

--Step 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME NOT LIKE 'dt_%' and ROUTINE_NAME NOT LIKE 'sp_MS%'
AND ROUTINE_TYPE = 'PROCEDURE'

--Step 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures

-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN

-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID

-- 7 - Build the string
SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user

-- 8 - Execute the string
-- SELECT @CMD1
EXEC(@CMD1)

-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END

-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures

SET NOCOUNT OFF
GO