IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CSODMGMT\SQLREWR')
CREATE USER [CSODMGMT\SQLREWR] FOR LOGIN [CSODMGMT\SQLREWR]
GO
EXEC sp_addrolemember N'db_datareader', N'CSODMGMT\SQLREWR'
GO
EXEC sp_addrolemember N'db_datawriter', N'CSODMGMT\SQLREWR'
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'SQLREWR')
DROP SCHEMA [SQLREWR]
GO
CREATE SCHEMA [SQLREWR] AUTHORIZATION [CSODMGMT\SQLREWR]
GO
GRANT CREATE TABLE TO [CSODMGMT\SQLREWR]
go
GRANT CREATE PROCEDURE TO [CSODMGMT\SQLREWR]
go
----------------------------------
declare @name nvarchar(1000), @string nvarchar(4000),@user nvarchar(100)
set @user = 'CSODMGMT\SQLREWR'
DECLARE Cur_name CURSOR FOR
select specific_name From information_Schema.routines
OPEN Cur_name
FETCH NEXT FROM Cur_name INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print '================================='
print @name
print '================================='
set @string ='GRANT VIEW DEFINITION ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string
set @string ='GRANT EXECUTE ON [dbo].['+@name+'] TO ['+@user+']'
print @string
exec sp_executesql @string
FETCH NEXT FROM Cur_name INTO @name
END
CLOSE Cur_name
DEALLOCATE Cur_name
GO
No comments:
Post a Comment