JavaEar 专注于收集分享传播有价值的技术资料

5个回答

    最佳答案
  1. You can modify the where clause of the object selection as you see fit. Should be minimal tweeking if you are looking for a 2005 or 2008 script.

    /Caution!/ This script can be a little dangerous.

    Declare @TableName varchar(100),
       @Sql nvarchar(500),
       @Result int,
       @UserName nvarchar(258)
       set @UserName= QuoteName('<your_user>')
    Print @UserName
    
      DECLARE
         Your_Cursor cursor
         LOCAL
         FORWARD_ONLY
         OPTIMISTIC
      FOR  
    /* if you only want one object to apply permissions to*/
    -- select Name from Sysobjects where name = 'Your_TableName'
    /*tables*/-- select name from sysobjects where xtype = 'U' order by name
    /*views*/-- select name from sysobjects where xtype = 'V' order by name
    /*StoredPs*/-- select name from sysobjects where xtype = 'P' order by name
    /*UDFs*/-- select name from sysobjects where xtype = 'FN' order by name
    /**********************************************************************/
    
    
    OPEN Your_Cursor
    FETCH NEXT from Your_Cursor into @TableName
    while (@@fetch_status = 0)
       begin
    /*Tables*/
    --      set @Sql = N'Grant Select On '+ @TableName+ N' To ' + @UserName
    --      set exec @Result = sp_executeSql @Sql
    --      if @Result = 0
    --        begin
    --          Print 'Granted Select On '+ @TableName + ' by ' + @UserName
    --        end
    --      set @Sql = N'Grant Insert On '+ @TableName+ N' To ' + @UserName
    --      set exec @Result = sp_executeSql @Sql
    --      if @Result = 0
    --        begin
    --          Print 'Granted Insert On '+ @TableName + ' by ' + @UserName
    --        end
    --      set @Sql = N'Grant Update On '+ @TableName+ N' To '+ @UserName
    --      set exec @Result = sp_executeSql @Sql
    --      if @Result = 0
    --        begin
    --          Print 'Granted Update On '+ @TableName + ' by ' + @UserName
    --        end
    --      set @Sql = N'Grant Delete On '+ @TableName+ N' To '+ @UserName
    --      set exec @Result = sp_executeSql @Sql
    --      if @Result = 0
    --        begin
    --          Print 'Granted Delete On '+ @TableName + ' by ' + @UserName
    --        end
    /*Stored Procs and UDFs*/
    --      set @Sql = N'Grant Execute On '+ @TableName+ N' To '+ @UserName
    --      set exec @Result = sp_executeSql @Sql
    --      if @Result = 0
    --        begin
    --          Print 'Granted Execute On '+ @TableName + ' by ' + @UserName
    --        end
         FETCH NEXT from your_Cursor into @TableName
       end
    CLOSE Your_Cursor
    DEALLOCATE Your_Cursor
    
  2. 参考答案2
  3. Link from RYU is dead.

    Found another script that does the job here:

    http://blogs.msdn.com/b/blogdoezequiel/archive/2010/04/26/the-sql-swiss-army-knife-1.aspx

    In case it ever dies here it is:

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[usp_SecurCreation]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[usp_SecurCreation]
    GO
    
    CREATE PROCEDURE usp_SecurCreation @User NVARCHAR(128) = NULL, @DB NVARCHAR(256) = NULL
    WITH ENCRYPTION
    AS
    -- 
    --  Does not deal with CERTIFICATE_MAPPED_LOGIN and ASYMMETRIC_KEY_MAPPED_LOGIN types 
    -- 
    --  All users: EXEC usp_SecurCreation 
    -- 
    --  One user, All DBs: EXEC usp_SecurCreation '<User>'
    --
    --  One user, One DBs: EXEC usp_SecurCreation '<User>', '<DBName>'
    --
    --  All users, One DBs: EXEC usp_SecurCreation NULL, '<DBName>'
    -- 
    
    SET NOCOUNT ON
    
    DECLARE @SC NVARCHAR(4000), @SCUser NVARCHAR(4000), @SCDB NVARCHAR(4000)
    CREATE TABLE #TempSecurables ([State] VARCHAR(100),
                [State2] VARCHAR(100),
                [PermName] VARCHAR(100),
                [Type] NVARCHAR(60),
                [Grantor] VARCHAR(100),
                [User] VARCHAR(100)
                )    
    CREATE TABLE #TempSecurables2 ([DBName] sysname,
                    [State] VARCHAR(1000)
                    )    
    
    IF @User IS NULL AND @DB IS NULL
    BEGIN
        --Server level Privileges to User or User Group
    
        INSERT INTO #TempSecurables
        SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END, 
        CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
        (SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
        FROM sys.server_permissions p JOIN sys.server_principals l 
        ON p.grantee_principal_id = l.principal_id
        AND l.is_disabled = 0
        AND l.type IN ('S', 'U', 'G', 'R')
    
        INSERT INTO #TempSecurables2
        EXEC master.dbo.sp_MSforeachdb @command1='USE [?] 
        --Privileges for Procedures/Functions/CLR/Views to the User
        SELECT ''[?]'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
        CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c 
        WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0 
        AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
        ORDER BY c.name
    
        --Table and View Level Privileges to the User
        SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
        WHERE GRANTEE <> ''public''
    
        --Column Level Privileges to the User 
        SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
        WHERE GRANTEE <> ''public'''
    END
    ELSE IF @User IS NULL AND @DB IS NOT NULL
    BEGIN
        --Server level Privileges to User or User Group
    
        INSERT INTO #TempSecurables
        SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END, 
        CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
        (SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
        FROM sys.server_permissions AS p JOIN sys.server_principals AS l 
        ON p.grantee_principal_id = l.principal_id
        AND l.is_disabled = 0
        AND l.type IN ('S', 'U', 'G', 'R')
    
        SET @SCDB='USE [' + @DB + '] 
        --Privileges for Procedures/Functions/CLR/Views to the User
        SELECT ''[' + @DB + ']'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
        CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c 
        WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0 
        AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
        ORDER BY c.name
    
        --Table and View Level Privileges to the User
        SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
        WHERE GRANTEE <> ''public''
    
        --Column Level Privileges to the User 
        SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
        WHERE GRANTEE <> ''public'''
    
        INSERT INTO #TempSecurables2
        EXEC master..sp_executesql @SCDB
    END
    ELSE IF @User IS NOT NULL AND @DB IS NOT NULL
    BEGIN
        --Server level Privileges to User or User Group
    
        INSERT INTO #TempSecurables
        SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END, 
        CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
        (SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
        FROM sys.server_permissions AS p JOIN sys.server_principals AS l 
        ON p.grantee_principal_id = l.principal_id
        AND l.is_disabled = 0
        AND l.type IN ('S', 'U', 'G', 'R')
        AND QUOTENAME(l.name) = QUOTENAME(@User)
    
        SET @SCDB='USE [' + @DB + '] 
        --Privileges for Procedures/Functions/CLR/Views to the User
        SELECT ''[' + @DB + ']'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
        CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c 
        WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0 
        AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
        AND QUOTENAME(USER_NAME(b.grantee_principal_id)) = ''[' + @User + ']''
        ORDER BY c.name
    
        --Table and View Level Privileges to the User
        SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
        WHERE GRANTEE <> ''public''
        AND grantee = ''[' + @User + ']''
    
        --Column Level Privileges to the User 
        SELECT ''[' + @DB + ']'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
        WHERE GRANTEE <> ''public''
        AND grantee = ''[' + @User + ']'''
    
        INSERT INTO #TempSecurables2
        EXEC master..sp_executesql @SCDB
    END
    ELSE
    BEGIN
        --Server level Privileges to User or User Group
        INSERT INTO #TempSecurables
        SELECT CASE CAST(p.state AS VARCHAR(100)) WHEN 'D' THEN 'DENY' WHEN 'R' THEN 'REVOKE' WHEN 'G' THEN 'GRANT' WHEN 'W' THEN 'GRANT' END, 
        CASE CAST(p.state AS VARCHAR(100)) WHEN 'W' THEN 'WITH GRANT OPTION' ELSE '' END, CAST(p.permission_name AS VARCHAR(100)), RTRIM(p.class_desc),
        (SELECT [name] FROM sys.server_principals WHERE principal_id = p.grantor_principal_id), CAST(l.name AS VARCHAR(100))
        FROM sys.server_permissions p JOIN sys.server_principals l 
        ON p.grantee_principal_id = l.principal_id
        AND l.is_disabled = 0
        AND l.type IN ('S', 'U', 'G', 'R')
        AND QUOTENAME(l.name) = QUOTENAME(@User)
    
        SET @SCUser = 'USE [?] 
        --Privileges for Procedures/Functions/CLR/Views to the User
        SELECT ''[?]'', CASE WHEN (b.state_desc COLLATE database_default) = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE (b.state_desc COLLATE database_default) END + '' EXECUTE ON ['' + c.name + ''].['' + a.name + ''] TO '' + QUOTENAME(USER_NAME(b.grantee_principal_id)) +
        CASE STATE WHEN ''W'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM sys.all_objects a, sys.database_permissions b, sys.schemas c 
        WHERE a.OBJECT_ID = b.major_id AND a.type IN (''X'',''P'',''FN'',''AF'',''FS'',''FT'') AND b.grantee_principal_id <>0 
        AND b.grantee_principal_id <>2 AND a.schema_id = c.schema_id
        AND QUOTENAME(USER_NAME(b.grantee_principal_id)) = ''[' + @User + ']''
        ORDER BY c.name
    
        --Table and View Level Privileges to the User
        SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
        WHERE GRANTEE <> ''public''
        AND grantee = ''[' + @User + ']''
    
        --Column Level Privileges to the User 
        SELECT ''[?]'', ''GRANT '' + privilege_type + '' ON ['' + table_schema + ''].['' + table_name + ''] ('' + column_name + '') TO ['' + grantee + '']'' +
        CASE IS_GRANTABLE WHEN ''YES'' THEN '' WITH GRANT OPTION'' 
        ELSE '''' END FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
        WHERE GRANTEE <> ''public''
        AND grantee = ''[' + @User + ']'''
    
        INSERT INTO #TempSecurables2
        EXEC master.dbo.sp_MSforeachdb @command1=@SCUser
    END
    
    DECLARE @tmpstr NVARCHAR(128)
    SET @tmpstr = '** Generated ' + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
    PRINT @tmpstr
    
    PRINT CHAR(13) + '--##### Server level Privileges to User or User Group #####' + CHAR(13)
    
    DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE [master];' + CHAR(10) + RTRIM(ts.[State]) + ' ' + RTRIM(ts.[PermName]) + ' TO ' + QUOTENAME(RTRIM(ts.[User])) + ' ' + RTRIM(ts.[State2]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables ts WHERE RTRIM([Type]) = 'SERVER'
    OPEN cSC  
    FETCH NEXT FROM cSC INTO @SC
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT @SC
            FETCH NEXT FROM cSC INTO @SC
        END
    CLOSE cSC 
    DEALLOCATE cSC
    
    DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE [master];' + CHAR(10) + RTRIM(ts.[State]) + ' ' + RTRIM(ts.[PermName]) + ' ON ' + CASE WHEN RTRIM(ts.[Type]) = 'SERVER_PRINCIPAL' THEN 'LOGIN' ELSE 'ENDPOINT' END + '::' + QUOTENAME(RTRIM(ts.[Grantor])) + ' TO ' + QUOTENAME(RTRIM(ts.[User])) + ' ' +RTRIM(ts.[State2]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables ts WHERE RTRIM([Type]) <> 'SERVER'
    OPEN cSC  
    FETCH NEXT FROM cSC INTO @SC
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT @SC
            FETCH NEXT FROM cSC INTO @SC
        END
    CLOSE cSC 
    DEALLOCATE cSC
    DROP TABLE #TempSecurables
    
    PRINT CHAR(13) + '--##### Procedures/Functions/CLR/Views, Table and Column Level Privileges to the User #####' + CHAR(13)
    
    DECLARE cSC CURSOR FAST_FORWARD FOR SELECT 'USE ' + ts2.DBName +';' + CHAR(10) + RTRIM(ts2.[State]) + ';' + CHAR(10) + 'GO' FROM #TempSecurables2 ts2
    OPEN cSC  
    FETCH NEXT FROM cSC INTO @SC
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
            PRINT @SC
            FETCH NEXT FROM cSC INTO @SC
        END
    CLOSE cSC 
    DEALLOCATE cSC
    
    DROP TABLE #TempSecurables2
    GO
    
  4. 参考答案3
  5. I had the same problem and solved it with Justins suggestion.

    • Right-click a template/example user in the database hive.
    • Choose properties, securables.
    • Make a change
    • Do Ctrl+Shift+N to get a script of the change.
  6. 参考答案4
  7. Back then I've created this small script to query permissions in our server. It was the first time I could make use of SQL cursors.. :) Hope this helps:

     --cursor
    drop table #permission_report
    --create #temp table
    CREATE TABLE #permission_report
       (db_name varchar(50),
      username varchar(50),
      objectname varchar(100),
      objectclass varchar(50),
      permission_name varchar(50),
      state varchar(50))

    declare @dbname VARCHAR(50)

    --declare cursor in order to run on every database on the server DECLARE c_dbnames CURSOR FOR SELECT name FROM sys.databases

    OPEN c_dbnames

    FETCH c_dbnames INTO @dbname WHILE @@Fetch_Status = 0 BEGIN --Openrowset to select the appropriate columns from system catalog views --insert result into #temp table --repeat task on every database on server EXEC('INSERT INTO #permission_report(db_name,username,objectname,objectclass,permission_name,state)

    SELECT '''+@dbname+''',p.name username, o.name objectname, class_desc,permission_name, state_desc FROM ' + @dbname +'.sys.database_principals p JOIN ' + @dbname +'.sys.database_permissions d ON d.grantee_principal_id = p.principal_id JOIN ' + @dbname +'.sys.objects o ON o.object_id = d.major_id where p.name=''Yourdomain\User''') ---checking only EMRSN\USMTN-FF20_Users FETCH c_dbnames INTO @dbname END CLOSE c_dbnames DEALLOCATE c_dbnames SELECT * FROM #permission_report
  8. 参考答案5
  9. As with anything SQL server when using SQL Server Management Studio if you aren't sure how to do a specific thing (i.e. looked in Books online but can't quite figure it out), using the 'Script Action to New Query Window' from the Script drop-down in the dialogs is very useful.

    By using the GUI tools then inspecting the resulting script you can quickly see how to do more complex things that you just can't keep in memory until you've done them loads of times.