Archive for tag: MS SQL Server

Assign user to membership database roles

USE AspNetAjax
GO
EXEC
sp_addrolemember 'aspnet_Personalization_FullAccess', 'AspNetAjaxUser'
EXEC sp_addrolemember 'aspnet_Profile_FullAccess', 'AspNetAjaxUser'
EXEC sp_addrolemember 'aspnet_Roles_FullAccess', 'AspNetAjaxUser'
EXEC sp_addrolemember 'aspnet_Membership_FullAccess', 'AspNetAjaxUser'
EXEC sp_addrolemember 'aspnet_WebEvent_FullAccess', 'AspNetAjaxUser'

Kill all processes connected to a database

-- Kill all processes connected to a database.
use master;
declare @DatabaseName varchar(50);
declare @Spid varchar(20);
declare @Command varchar(50);
set @DatabaseName = 'MyDatabase';
print 'This query''s SPID: ' + convert(varchar, @@spid);
-- Select all SPIDs except the SPID for this connection
declare SpidCursor cursor for
select spid from master.dbo.sysprocesses
where dbid = db_id(@DatabaseName)
and spid != @@spid
open SpidCursor
fetch next from SpidCursor into @spid
while @@fetch_status = 0
begin
 print 'Killing process: ' + rtrim(@spid);
 set @Command = 'kill ' + rtrim(@spid) + ';';
 print @Command;
 execute(@Command);
 fetch next from SpidCursor into @spid
end
close SpidCursor
deallocate SpidCursor

Grant execute on all stored procedures in current db

Sample showing how to grant execute on all stored procedures for a specific user in specified database

USE [The Database Name Here]
DECLARE @name nvarchar(128)
DECLARE procCursor CURSOR FOR SELECT Name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1
 
OPEN procCursor
FETCH NEXT FROM procCursor INTO @name
 
WHILE @@FETCH_STATUS= 0
BEGIN
EXEC ('grant execute on [' +@name + '] to[The Database User Here]')
 
FETCH NEXT FROM procCursor INTO @name
END
 
CLOSE procCursor
DEALLOCATE procCursor
GO