Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT name, base_object_name
- FROM sys.synonyms
- WHERE schema_id IN (SELECT schema_id
- FROM sys.schemas
- WHERE name = N'XXXX')
- ORDER BY name
- EXEC sp_addrolemember N'db_datareader', N'UserB'
- EXEC sp_addrolemember N'db_datawriter', N'UserB'
- EXEC sp_addrolemember N'db_ddladmin', N'UserB'
- GRANT EXECUTE TO [UserB]
- GRANT CREATE SCHEMA TO [UserB]
- GRANT VIEW DEFINITION TO [UserB]
- declare @id int
- SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
- SELECT a.name, base_object_name FROM sys.synonyms a
- WHERE schema_id = @id
- ORDER BY name
- declare @id int
- SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
- SELECT a.name, base_object_name FROM sys.synonyms a
- WHERE schema_id = @id
- ORDER BY name
- declare @id int
- SELECT @id=schema_id FROM sys.schemas WHERE name = N'XXXX'
- SELECT a.name, base_object_name FROM sys.synonyms a
- WHERE schema_id = @id
- ORDER BY name
- DECLARE @TranName VARCHAR(20)
- SELECT @TranName = 'MyTransaction'
- BEGIN TRANSACTION @TranName
- GO
- IF NOT EXISTS (SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
- WHERE SCHEMA_NAME = '{1}')
- BEGIN
- EXEC('CREATE SCHEMA [{1}]')
- EXEC sp_addextendedproperty @name='User', @value='{0}', @level0type=N'Schema', @level0name=N'{1}'
- END
- GO
- {2}
- COMMIT TRANSACTION MyTransaction;
- GO
- SELECT name, base_object_name
- FROM sys.synonyms
- WHERE schema_id = SCHEMA_ID(N'dbo')
- ORDER BY name
- SELECT COUNT(*)
- FROM sys.objects
Add Comment
Please, Sign In to add comment