Verifying a user's permissions on SQL Server

Views: 28.409 views

In this article

Reading Time: 15 minutes

Hello people!
Good day.

In this post, I will demonstrate how to verify the permissions of a user and / or database in SQL Server, as well as allowing you to easily remove these access or grant again. If you want to replicate a user's permissions between different instances, see if the article SQL Server - How to copy / replicate a user's permissions can help you.

Using system SP, sp_helprotect

Using the stored procedure sp_helprotect, we can easily list the permissions of a particular user or object:

-- Verificando as permissões do usuário "Usuario_Teste"
EXEC Protheus_Producao.dbo.sp_helprotect 
    @username = 'Usuario_Teste'

-- Verificando as permissões do usuário "Usuario_Teste"

EXEC Protheus_Producao.dbo.sp_helprotect

@username = 'Usuario_Teste'

Result

-- Verificando as permissões da tabela "SA1010"
EXEC Protheus_Producao.dbo.sp_helprotect 
    @name = 'SA1010'

-- Verificando as permissões da tabela "SA1010"

EXEC Protheus_Producao.dbo.sp_helprotect

@name = 'SA1010'

Result

Using Transact SQL Queries in Catalog Views

A very interesting way to get and work with this information that we saw in the SQL Server Managment Studio interface is by using SQL queries and SQL Server database catalog views. These queries bring more information than the sp_helprotect system sp and just like using SP we can work with the returned data.

View source

DECLARE
    @Ds_Usuario VARCHAR(100) = 'Usuario_Teste'


-- Associação Usuário x Login
IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins
SELECT
    C.name AS Ds_Login,
    B.name AS Ds_Usuario
INTO
    #Users_Logins
FROM 
    sys.database_principals				A	WITH(NOLOCK)
    JOIN sys.sysusers					B	WITH(NOLOCK)	ON	A.principal_id = B.uid
    LEFT JOIN sys.syslogins				C	WITH(NOLOCK)	ON	B.sid = C.sid
WHERE
    A.type_desc != 'DATABASE_ROLE'
    AND (C.name = @Ds_Usuario OR B.name = @Ds_Usuario OR @Ds_Usuario IS NULL)



-- Recupera o Login e o usuário
DECLARE
    @Ds_Usuario_Recuperado VARCHAR(MAX),
    @Ds_Login_Recuperado VARCHAR(MAX)


SELECT
    @Ds_Login_Recuperado = Ds_Login,
    @Ds_Usuario_Recuperado = Ds_Usuario
FROM
    #Users_Logins



-- Database Roles
IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles
SELECT 
    C.name AS Ds_Usuario,
    B.name AS Ds_Database_Role
INTO
    #Database_Roles
FROM 
    sys.database_role_members		A	WITH(NOLOCK)
    JOIN sys.database_principals	B	WITH(NOLOCK)	ON	A.role_principal_id = B.principal_id
    JOIN sys.sysusers			C	WITH(NOLOCK)	ON	A.member_principal_id = C.uid
WHERE
    (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)



-- Database Permissions
IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions
SELECT
    A.class_desc AS Ds_Tipo_Permissao, 
    A.permission_name AS Ds_Permissao,
    A.state_desc AS Ds_Operacao,
    B.name AS Ds_Usuario_Permissao,
    C.name AS Ds_Login_Permissao,
    D.name AS Ds_Objeto
INTO
    #Database_Permissions
FROM 
    sys.database_permissions				A	WITH(NOLOCK)
    JOIN sys.sysusers					B	WITH(NOLOCK)	ON	A.grantee_principal_id = B.uid
    LEFT JOIN sys.syslogins				C	WITH(NOLOCK)	ON	B.sid = C.sid
    LEFT JOIN sys.objects				D	WITH(NOLOCK)	ON	A.major_id = D.object_id
WHERE
    A.major_id >= 0
    AND (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)
    


-- Server roles
IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles
SELECT 
    B.name AS Ds_Usuario,
    C.name AS Ds_Server_Role
INTO
    #Server_Roles
FROM 
    sys.server_role_members		A	WITH(NOLOCK)
    JOIN sys.server_principals		B	WITH(NOLOCK)	ON	A.member_principal_id = B.principal_id
    JOIN sys.server_principals		C	WITH(NOLOCK)	ON	A.role_principal_id = C.principal_id
WHERE
    (B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)
    


-- Server permissions
IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions
SELECT
    A.class_desc AS Ds_Tipo_Permissao,
    A.state_desc AS Ds_Tipo_Operacao,
    A.permission_name AS Ds_Permissao,
    C.name AS Ds_Login,
    B.type_desc AS Ds_Tipo_Login
INTO
    #Server_Permissions
FROM 
    sys.server_permissions				A	WITH(NOLOCK)
    JOIN sys.server_principals				B	WITH(NOLOCK)	ON	A.grantee_principal_id = B.principal_id
    LEFT JOIN sys.syslogins				C	WITH(NOLOCK)	ON	B.sid = C.sid
WHERE
    (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)


SELECT * FROM #Users_Logins
SELECT * FROM #Database_Roles
SELECT * FROM #Database_Permissions
SELECT * FROM #Server_Roles
SELECT * FROM #Server_Permissions

100

101

102

103

104

105

106

107

108

109

110

111

112

DECLARE

@Ds_Usuario VARCHAR(100) = 'Usuario_Teste'

-- Associação Usuário x Login

IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins

SELECT

C.name AS Ds_Login,

B.name AS Ds_Usuario

INTO

#Users_Logins

FROM

sys.database_principals A WITH(NOLOCK)

JOIN sys.sysusers B WITH(NOLOCK) ON A.principal_id = B.uid

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

WHERE

A.type_desc != 'DATABASE_ROLE'

AND (C.name = @Ds_Usuario OR B.name = @Ds_Usuario OR @Ds_Usuario IS NULL)

-- Recupera o Login e o usuário

DECLARE

@Ds_Usuario_Recuperado VARCHAR(MAX),

@Ds_Login_Recuperado VARCHAR(MAX)

SELECT

@Ds_Login_Recuperado = Ds_Login,

@Ds_Usuario_Recuperado = Ds_Usuario

FROM

#Users_Logins

-- Database Roles

IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles

SELECT

C.name AS Ds_Usuario,

B.name AS Ds_Database_Role

INTO

#Database_Roles

FROM

sys.database_role_members A WITH(NOLOCK)

JOIN sys.database_principals B WITH(NOLOCK) ON A.role_principal_id = B.principal_id

JOIN sys.sysusers C WITH(NOLOCK) ON A.member_principal_id = C.uid

WHERE

(C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)

-- Database Permissions

IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions

SELECT

A.class_desc AS Ds_Tipo_Permissao,

A.permission_name AS Ds_Permissao,

A.state_desc AS Ds_Operacao,

B.name AS Ds_Usuario_Permissao,

C.name AS Ds_Login_Permissao,

D.name AS Ds_Objeto

INTO

#Database_Permissions

FROM

sys.database_permissions A WITH(NOLOCK)

JOIN sys.sysusers B WITH(NOLOCK) ON A.grantee_principal_id = B.uid

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

LEFT JOIN sys.objects D WITH(NOLOCK) ON A.major_id = D.object_id

WHERE

A.major_id >= 0

AND (C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)

-- Server roles

IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles

SELECT

B.name AS Ds_Usuario,

C.name AS Ds_Server_Role

INTO

#Server_Roles

FROM

sys.server_role_members A WITH(NOLOCK)

JOIN sys.server_principals B WITH(NOLOCK) ON A.member_principal_id = B.principal_id

JOIN sys.server_principals C WITH(NOLOCK) ON A.role_principal_id = C.principal_id

WHERE

(B.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)

-- Server permissions

IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions

SELECT

A.class_desc AS Ds_Tipo_Permissao,

A.state_desc AS Ds_Tipo_Operacao,

A.permission_name AS Ds_Permissao,

C.name AS Ds_Login,

B.type_desc AS Ds_Tipo_Login

INTO

#Server_Permissions

FROM

sys.server_permissions A WITH(NOLOCK)

JOIN sys.server_principals B WITH(NOLOCK) ON A.grantee_principal_id = B.principal_id

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

WHERE

(C.name IN (@Ds_Usuario_Recuperado, @Ds_Login_Recuperado) OR @Ds_Usuario IS NULL)

SELECT * FROM #Users_Logins

SELECT * FROM #Database_Roles

SELECT * FROM #Database_Permissions

SELECT * FROM #Server_Roles

SELECT * FROM #Server_Permissions

Return of Queries

Using a Custom Stored Procedure

With a little more work and refinement, we can use a Stored Procedure for this purpose. We can use feedback to generate an access report, record the information for history, etc. See how easy it is:

View SP source code

USE [Auditoria]
GO
/****** Object:  StoredProcedure [dbo].[stpVerifica_Permissoes]    Script Date: 09/07/2015 15:18:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stpVerifica_Permissoes] (
    @Ds_Usuario VARCHAR(100) = NULL,
    @Ds_Database VARCHAR(100) = NULL,
    @Ds_Objeto VARCHAR(100) = NULL,
    @Nr_Tipo_Permissao SMALLINT = NULL,
    @Fl_Permissoes_Servidor BIT = 1
)
AS BEGIN
        

    SELECT
        @Ds_Usuario = ISNULL(@Ds_Usuario, ''),
        @Ds_Database = ISNULL(@Ds_Database, ''),
        @Ds_Objeto = ISNULL(@Ds_Objeto, '')

        
    DECLARE
        @Query VARCHAR(MAX)



    ----------------------------------------------------------------------------------------
    -- ASSOCIAÇÃO USUÁRIO X LOGIN
    ----------------------------------------------------------------------------------------
    
    IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins
    CREATE TABLE #Users_Logins (
        Ds_Database VARCHAR(100),
        Ds_Login VARCHAR(100),
        Ds_Usuario VARCHAR(100)
    )
    
    
    IF (@Nr_Tipo_Permissao = 0 OR @Nr_Tipo_Permissao IS NULL)
    BEGIN
    
        SET @Query = '
        IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')
        BEGIN

            USE [?]

            SELECT
                ''?'' AS Ds_Database,
                C.name AS Ds_Login,
                B.name AS Ds_Usuario
            FROM 
                sys.database_principals				A	WITH(NOLOCK)
                JOIN sys.sysusers				B	WITH(NOLOCK)	ON	A.principal_id = B.uid
                LEFT JOIN sys.syslogins				C	WITH(NOLOCK)	ON	B.sid = C.sid
            WHERE
                A.type_desc != ''DATABASE_ROLE''
                AND (C.name = ''' + @Ds_Usuario + ''' OR B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')
                AND (C.name = ''' + @Ds_Objeto + ''' OR B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')
                
        END'
        

        INSERT INTO #Users_Logins
        EXEC master.dbo.sp_MSforeachdb @Query
        
    END



    ----------------------------------------------------------------------------------------
    -- PERMISSÕES EM ROLES DE BANCO DE DADOS
    ----------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles
    CREATE TABLE #Database_Roles (
        Ds_Database VARCHAR(100),
        Ds_Login VARCHAR(100),
        Ds_Usuario VARCHAR(100),
        Ds_Database_Role VARCHAR(100)
    )
    
    
    IF (@Nr_Tipo_Permissao = 1 OR @Nr_Tipo_Permissao IS NULL)
    BEGIN

        SET @Query = '
        IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')
        BEGIN

            USE [?]

            SELECT 
                ''?'' AS Ds_Database,
                D.name AS Ds_Login,
                C.name AS Ds_Usuario,
                B.name AS Ds_Role
            FROM 
                sys.database_role_members		A	WITH(NOLOCK)
                JOIN sys.database_principals		B	WITH(NOLOCK)	ON	A.role_principal_id = B.principal_id
                JOIN sys.sysusers			C	WITH(NOLOCK)	ON	A.member_principal_id = C.uid
                LEFT JOIN sys.syslogins			D	WITH(NOLOCK)	ON	C.sid = D.sid
            WHERE
                (C.name = ''' + @Ds_Usuario + ''' OR D.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario  + ''' = '''')
                AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')
                
        END'


        INSERT INTO #Database_Roles
        EXEC master.dbo.sp_MSforeachdb @Query
        
    END


    ----------------------------------------------------------------------------------------
    -- PERMISSÕES A NÍVEL DE BANCO DE DADOS
    ----------------------------------------------------------------------------------------
    
    IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions
    CREATE TABLE #Database_Permissions (
        Ds_Database VARCHAR(100),
        Ds_Tipo_Permissao VARCHAR(60),
        Ds_Permissao VARCHAR(128),
        Ds_Operacao VARCHAR(60),
        Ds_Login_Permissao VARCHAR(100),
        Ds_Usuario_Permissao VARCHAR(100),
        Ds_Objeto VARCHAR(100)
    )
    
    
    IF (@Nr_Tipo_Permissao = 2 OR @Nr_Tipo_Permissao IS NULL)
    BEGIN
    
        SET @Query = '
        IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')
        BEGIN

            USE [?]

            SELECT
                ''?'' AS Ds_Database,
                A.class_desc AS Ds_Tipo_Permissao, 
                A.permission_name AS Ds_Permissao,
                A.state_desc AS Ds_Operacao,
                C.name AS Ds_Login_Permissao,
                B.name AS Ds_Usuario_Permissao,
                D.name AS Ds_Objeto
            FROM 
                sys.database_permissions				A	WITH(NOLOCK)
                JOIN sys.sysusers					B	WITH(NOLOCK)	ON	A.grantee_principal_id = B.uid
                LEFT JOIN sys.syslogins					C	WITH(NOLOCK)	ON	B.sid = C.sid
                LEFT JOIN sys.objects					D	WITH(NOLOCK)	ON	A.major_id = D.object_id
            WHERE
                A.major_id >= 0
                AND (B.name = ''' + @Ds_Usuario + ''' OR C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')
                AND (D.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')
                
        END'


        INSERT INTO #Database_Permissions
        EXEC master.dbo.sp_MSforeachdb @Query
        
    END


    ----------------------------------------------------------------------------------------
    -- PERMISSÕES EM ROLES DE SISTEMA
    ----------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles
    CREATE TABLE #Server_Roles (
        Ds_Usuario VARCHAR(100),
        Ds_Server_Role VARCHAR(100)
    )

    
    IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 3)
    BEGIN

    
        SET @Query = '
        SELECT 
            B.name AS Ds_Usuario,
            C.name AS Ds_Role
        FROM 
            sys.server_role_members			A	WITH(NOLOCK)
            JOIN sys.server_principals			B	WITH(NOLOCK)	ON	A.member_principal_id = B.principal_id
            JOIN sys.server_principals			C	WITH(NOLOCK)	ON	A.role_principal_id = C.principal_id
        WHERE
            (B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')
            AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')'


        INSERT INTO #Server_Roles
        EXEC (@Query)
        
    END


    ----------------------------------------------------------------------------------------
    -- PERMISSÕES A NÍVEL DE SERVIDOR
    ----------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions
    CREATE TABLE #Server_Permissions (
        Ds_Tipo_Permissao VARCHAR(60),
        Ds_Tipo_Operacao VARCHAR(60),
        Ds_Permissao VARCHAR(128),
        Ds_Login VARCHAR(100),
        Ds_Tipo_Login VARCHAR(100)
    )


    IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 4)
    BEGIN


        SET @Query = '
        SELECT
            A.class_desc AS Ds_Tipo_Permissao,
            A.state_desc AS Ds_Tipo_Operacao,
            A.permission_name AS Ds_Permissao,
            C.name AS Ds_Login,
            B.type_desc AS Ds_Tipo_Login
        FROM 
            sys.server_permissions					A	WITH(NOLOCK)
            JOIN sys.server_principals					B	WITH(NOLOCK)	ON	A.grantee_principal_id = B.principal_id
            LEFT JOIN sys.syslogins					C	WITH(NOLOCK)	ON	B.sid = C.sid
        WHERE
            (C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')
            AND (C.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')'


        INSERT INTO #Server_Permissions
        EXEC(@Query)
        
        
    END


    ----------------------------------------------------------------------------------------
    -- DEFINE AS SAÍDAS
    ----------------------------------------------------------------------------------------

    SELECT 
        0 AS Id_Nivel_Permissao,
        'User_Login' AS Ds_Nivel_Permissao,
        Ds_Database,
        NULL AS Ds_Tipo_Permissao,
        'LOGIN' AS Ds_Permissao,
        'GRANT' AS Ds_Operacao,
        Ds_Login, 
        Ds_Usuario,
        NULL AS Ds_Objeto
    FROM 
        #Users_Logins
    
    UNION ALL
    
    SELECT 
        1 AS Id_Nivel_Permissao,
        'Database_Role' AS Ds_Nivel_Permissao,
        Ds_Database,
        NULL AS Ds_Tipo_Permissao,
        Ds_Database_Role AS Ds_Permissao,
        'GRANT' AS Ds_Operacao,
        Ds_Login,
        Ds_Usuario,
        NULL AS Ds_Objeto
    FROM 
        #Database_Roles
        
    UNION ALL
        
    SELECT 
        2 AS Id_Nivel_Permissao,
        'Database_Permission' AS Ds_Nivel_Permissao,
        Ds_Database,
        Ds_Tipo_Permissao,
        Ds_Permissao,
        Ds_Operacao,
        Ds_Login_Permissao,
        Ds_Usuario_Permissao,
        Ds_Objeto
    FROM 
        #Database_Permissions
        
    UNION ALL
    
    SELECT 
        3 AS Id_Nivel_Permissao,
        'Server_Role' AS Ds_Nivel_Permissao,
        NULL AS Ds_Database,
        NULL AS Ds_Tipo_Permissao,
        Ds_Server_Role AS Ds_Permissao,
        'GRANT' AS Ds_Operacao,
        Ds_Usuario AS Ds_Login,
        NULL AS Ds_Usuario,
        @@SERVERNAME AS Ds_Objeto
    FROM 
        #Server_Roles
        
    UNION ALL
    
    SELECT 
        4 AS Id_Nivel_Permissao,
        'Server_Permission' AS Ds_Nivel_Permissao,
        NULL AS Ds_Database,
        Ds_Tipo_Permissao,
        Ds_Permissao,
        Ds_Tipo_Operacao AS Ds_Operacao,
        Ds_Login,
        NULL AS Ds_Usuario,
        @@SERVERNAME AS Ds_Objeto
    FROM 
        #Server_Permissions
    ORDER BY
        1, 3, 7, 8, 9
    

END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

USE [Auditoria]

/****** Object: StoredProcedure [dbo].[stpVerifica_Permissoes] Script Date: 09/07/2015 15:18:24 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[stpVerifica_Permissoes] (

@Ds_Usuario VARCHAR(100) = NULL,

@Ds_Database VARCHAR(100) = NULL,

@Ds_Objeto VARCHAR(100) = NULL,

@Nr_Tipo_Permissao SMALLINT = NULL,

@Fl_Permissoes_Servidor BIT = 1

)

AS BEGIN

SELECT

@Ds_Usuario = ISNULL(@Ds_Usuario, ''),

@Ds_Database = ISNULL(@Ds_Database, ''),

@Ds_Objeto = ISNULL(@Ds_Objeto, '')

DECLARE

@Query VARCHAR(MAX)

----------------------------------------------------------------------------------------

-- ASSOCIAÇÃO USUÁRIO X LOGIN

----------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Users_Logins') IS NOT NULL) DROP TABLE #Users_Logins

CREATE TABLE #Users_Logins (

Ds_Database VARCHAR(100),

Ds_Login VARCHAR(100),

Ds_Usuario VARCHAR(100)

)

IF (@Nr_Tipo_Permissao = 0 OR @Nr_Tipo_Permissao IS NULL)

BEGIN

SET @Query = '

IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')

BEGIN

USE [?]

SELECT

''?'' AS Ds_Database,

C.name AS Ds_Login,

B.name AS Ds_Usuario

FROM

sys.database_principals A WITH(NOLOCK)

JOIN sys.sysusers B WITH(NOLOCK) ON A.principal_id = B.uid

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

WHERE

A.type_desc != ''DATABASE_ROLE''

AND (C.name = ''' + @Ds_Usuario + ''' OR B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')

AND (C.name = ''' + @Ds_Objeto + ''' OR B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')

END'

INSERT INTO #Users_Logins

EXEC master.dbo.sp_MSforeachdb @Query

END

----------------------------------------------------------------------------------------

-- PERMISSÕES EM ROLES DE BANCO DE DADOS

----------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Database_Roles') IS NOT NULL) DROP TABLE #Database_Roles

CREATE TABLE #Database_Roles (

Ds_Database VARCHAR(100),

Ds_Login VARCHAR(100),

Ds_Usuario VARCHAR(100),

Ds_Database_Role VARCHAR(100)

)

IF (@Nr_Tipo_Permissao = 1 OR @Nr_Tipo_Permissao IS NULL)

BEGIN

SET @Query = '

IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')

BEGIN

USE [?]

SELECT

''?'' AS Ds_Database,

D.name AS Ds_Login,

C.name AS Ds_Usuario,

B.name AS Ds_Role

FROM

sys.database_role_members A WITH(NOLOCK)

JOIN sys.database_principals B WITH(NOLOCK) ON A.role_principal_id = B.principal_id

JOIN sys.sysusers C WITH(NOLOCK) ON A.member_principal_id = C.uid

LEFT JOIN sys.syslogins D WITH(NOLOCK) ON C.sid = D.sid

WHERE

(C.name = ''' + @Ds_Usuario + ''' OR D.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')

AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')

END'

INSERT INTO #Database_Roles

EXEC master.dbo.sp_MSforeachdb @Query

END

----------------------------------------------------------------------------------------

-- PERMISSÕES A NÍVEL DE BANCO DE DADOS

----------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Database_Permissions') IS NOT NULL) DROP TABLE #Database_Permissions

CREATE TABLE #Database_Permissions (

Ds_Database VARCHAR(100),

Ds_Tipo_Permissao VARCHAR(60),

Ds_Permissao VARCHAR(128),

Ds_Operacao VARCHAR(60),

Ds_Login_Permissao VARCHAR(100),

Ds_Usuario_Permissao VARCHAR(100),

Ds_Objeto VARCHAR(100)

)

IF (@Nr_Tipo_Permissao = 2 OR @Nr_Tipo_Permissao IS NULL)

BEGIN

SET @Query = '

IF (''?'' = ''' + @Ds_Database + ''' OR ''' + @Ds_Database + ''' = '''')

BEGIN

USE [?]

SELECT

''?'' AS Ds_Database,

A.class_desc AS Ds_Tipo_Permissao,

A.permission_name AS Ds_Permissao,

A.state_desc AS Ds_Operacao,

C.name AS Ds_Login_Permissao,

B.name AS Ds_Usuario_Permissao,

D.name AS Ds_Objeto

FROM

sys.database_permissions A WITH(NOLOCK)

JOIN sys.sysusers B WITH(NOLOCK) ON A.grantee_principal_id = B.uid

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

LEFT JOIN sys.objects D WITH(NOLOCK) ON A.major_id = D.object_id

WHERE

A.major_id >= 0

AND (B.name = ''' + @Ds_Usuario + ''' OR C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')

AND (D.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')

END'

INSERT INTO #Database_Permissions

EXEC master.dbo.sp_MSforeachdb @Query

END

----------------------------------------------------------------------------------------

-- PERMISSÕES EM ROLES DE SISTEMA

----------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Server_Roles') IS NOT NULL) DROP TABLE #Server_Roles

CREATE TABLE #Server_Roles (

Ds_Usuario VARCHAR(100),

Ds_Server_Role VARCHAR(100)

)

IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 3)

BEGIN

SET @Query = '

SELECT

B.name AS Ds_Usuario,

C.name AS Ds_Role

FROM

sys.server_role_members A WITH(NOLOCK)

JOIN sys.server_principals B WITH(NOLOCK) ON A.member_principal_id = B.principal_id

JOIN sys.server_principals C WITH(NOLOCK) ON A.role_principal_id = C.principal_id

WHERE

(B.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')

AND (B.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')'

INSERT INTO #Server_Roles

EXEC (@Query)

END

----------------------------------------------------------------------------------------

-- PERMISSÕES A NÍVEL DE SERVIDOR

----------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Server_Permissions') IS NOT NULL) DROP TABLE #Server_Permissions

CREATE TABLE #Server_Permissions (

Ds_Tipo_Permissao VARCHAR(60),

Ds_Tipo_Operacao VARCHAR(60),

Ds_Permissao VARCHAR(128),

Ds_Login VARCHAR(100),

Ds_Tipo_Login VARCHAR(100)

)

IF ((@Fl_Permissoes_Servidor = 1 AND @Nr_Tipo_Permissao IS NULL) OR @Nr_Tipo_Permissao = 4)

BEGIN

SET @Query = '

SELECT

A.class_desc AS Ds_Tipo_Permissao,

A.state_desc AS Ds_Tipo_Operacao,

A.permission_name AS Ds_Permissao,

C.name AS Ds_Login,

B.type_desc AS Ds_Tipo_Login

FROM

sys.server_permissions A WITH(NOLOCK)

JOIN sys.server_principals B WITH(NOLOCK) ON A.grantee_principal_id = B.principal_id

LEFT JOIN sys.syslogins C WITH(NOLOCK) ON B.sid = C.sid

WHERE

(C.name = ''' + @Ds_Usuario + ''' OR ''' + @Ds_Usuario + ''' = '''')

AND (C.name = ''' + @Ds_Objeto + ''' OR ''' + @Ds_Objeto + ''' = '''')'

INSERT INTO #Server_Permissions

EXEC(@Query)

END

----------------------------------------------------------------------------------------

-- DEFINE AS SAÍDAS

----------------------------------------------------------------------------------------

SELECT

0 AS Id_Nivel_Permissao,

'User_Login' AS Ds_Nivel_Permissao,

Ds_Database,

NULL AS Ds_Tipo_Permissao,

'LOGIN' AS Ds_Permissao,

'GRANT' AS Ds_Operacao,

Ds_Login,

Ds_Usuario,

NULL AS Ds_Objeto

FROM

#Users_Logins

UNION ALL

SELECT

1 AS Id_Nivel_Permissao,

'Database_Role' AS Ds_Nivel_Permissao,

Ds_Database,

NULL AS Ds_Tipo_Permissao,

Ds_Database_Role AS Ds_Permissao,

'GRANT' AS Ds_Operacao,

Ds_Login,

Ds_Usuario,

NULL AS Ds_Objeto

FROM

#Database_Roles

UNION ALL

SELECT

2 AS Id_Nivel_Permissao,

'Database_Permission' AS Ds_Nivel_Permissao,

Ds_Database,

Ds_Tipo_Permissao,

Ds_Permissao,

Ds_Operacao,

Ds_Login_Permissao,

Ds_Usuario_Permissao,

Ds_Objeto

FROM

#Database_Permissions

UNION ALL

SELECT

3 AS Id_Nivel_Permissao,

'Server_Role' AS Ds_Nivel_Permissao,

NULL AS Ds_Database,

NULL AS Ds_Tipo_Permissao,

Ds_Server_Role AS Ds_Permissao,

'GRANT' AS Ds_Operacao,

Ds_Usuario AS Ds_Login,

NULL AS Ds_Usuario,

@@SERVERNAME AS Ds_Objeto

FROM

#Server_Roles

UNION ALL

SELECT

4 AS Id_Nivel_Permissao,

'Server_Permission' AS Ds_Nivel_Permissao,

NULL AS Ds_Database,

Ds_Tipo_Permissao,

Ds_Permissao,

Ds_Tipo_Operacao AS Ds_Operacao,

Ds_Login,

NULL AS Ds_Usuario,

@@SERVERNAME AS Ds_Objeto

FROM

#Server_Permissions

ORDER BY

1, 3, 7, 8, 9

END

Usage examples

-- Verifica todas as permissões do usuário 'Usuario_Teste' na instância
EXEC dbo.stpVerifica_Permissoes
    @Ds_Usuario = 'Usuario_Teste'
    
    
-- Verifica todas as permissões da tabela 'SA1010' no database 'Protheus_Producao'
EXEC dbo.stpVerifica_Permissoes
    @Ds_Database = 'Protheus_Producao',
    @Ds_Objeto = 'SA1010'
    

-- Verifica as roles de database do usuário 'Usuario_Teste' em todos os bancos
EXEC dbo.stpVerifica_Permissoes
    @Ds_Usuario = 'Usuario_Teste', -- varchar(100)
    @Ds_Database = NULL, -- varchar(100)
    @Ds_Objeto = NULL,
    @Nr_Tipo_Permissao = 1,
    @Fl_Permissoes_Servidor = 0 -- Não
    
    
-- Verifica as permissões a nível de Database do usuário 'Usuario_Teste'
EXEC dbo.stpVerifica_Permissoes
    @Ds_Usuario = 'Usuario_Teste', -- varchar(100)
    @Ds_Database = NULL, -- varchar(100)
    @Ds_Objeto = NULL,
    @Nr_Tipo_Permissao = 2,
    @Fl_Permissoes_Servidor = 0 -- Não
    
    
-- Verifica as permissões do database 'Protheus_Producao' para todos os usuários
EXEC dbo.stpVerifica_Permissoes
    @Ds_Usuario = NULL, -- varchar(100)
    @Ds_Database = 'Protheus_Producao', -- varchar(100)
    @Ds_Objeto = NULL,
    @Nr_Tipo_Permissao = 2,
    @Fl_Permissoes_Servidor = 0 -- Não
    
    
-- Verifica as permissões a nível de sistema da instância
EXEC dbo.stpVerifica_Permissoes
    @Nr_Tipo_Permissao = 4
    
   
-- Verifica os membros de roles de sistema da instância
EXEC dbo.stpVerifica_Permissoes
    @Nr_Tipo_Permissao = 3

-- Verifica todas as permissões do usuário 'Usuario_Teste' na instância

EXEC dbo.stpVerifica_Permissoes

@Ds_Usuario = 'Usuario_Teste'

-- Verifica todas as permissões da tabela 'SA1010' no database 'Protheus_Producao'

EXEC dbo.stpVerifica_Permissoes

@Ds_Database = 'Protheus_Producao',

@Ds_Objeto = 'SA1010'

-- Verifica as roles de database do usuário 'Usuario_Teste' em todos os bancos

EXEC dbo.stpVerifica_Permissoes

@Ds_Usuario = 'Usuario_Teste', -- varchar(100)

@Ds_Database = NULL, -- varchar(100)

@Ds_Objeto = NULL,

@Nr_Tipo_Permissao = 1,

@Fl_Permissoes_Servidor = 0 -- Não

-- Verifica as permissões a nível de Database do usuário 'Usuario_Teste'

EXEC dbo.stpVerifica_Permissoes

@Ds_Usuario = 'Usuario_Teste', -- varchar(100)

@Ds_Database = NULL, -- varchar(100)

@Ds_Objeto = NULL,

@Nr_Tipo_Permissao = 2,

@Fl_Permissoes_Servidor = 0 -- Não

-- Verifica as permissões do database 'Protheus_Producao' para todos os usuários

EXEC dbo.stpVerifica_Permissoes

@Ds_Usuario = NULL, -- varchar(100)

@Ds_Database = 'Protheus_Producao', -- varchar(100)

@Ds_Objeto = NULL,

@Nr_Tipo_Permissao = 2,

@Fl_Permissoes_Servidor = 0 -- Não

-- Verifica as permissões a nível de sistema da instância

EXEC dbo.stpVerifica_Permissoes

@Nr_Tipo_Permissao = 4

-- Verifica os membros de roles de sistema da instância

EXEC dbo.stpVerifica_Permissoes

@Nr_Tipo_Permissao = 3

Return Example (First Example)

Using a Stored Procedure with GRANT and REVOKE Code

Like the previous Stored Procedure, this version is a little more "compact" and still generates the T-SQL codes to grant this privilege (if you want to make a backup of the existing permissions) and also the T-SQL code to remove this permission.

View stp's codeVerify_User_Permissions

IF (OBJECT_ID('dbo.stpVerifica_Permissoes_Usuario') IS NULL) EXEC('CREATE PROCEDURE dbo.stpVerifica_Permissoes_Usuario AS SELECT 1')
GO

ALTER PROCEDURE [dbo].[stpVerifica_Permissoes_Usuario]
    @Ds_Database [varchar](50) = NULL,
    @Ds_Usuario [varchar](50) = NULL,
    @Fl_Objetos_Sistema BIT = 0,
    @Fl_Permissoes_Instancia BIT = 1,
    @Fl_Permissoes_Banco BIT = 1
AS
BEGIN


    -- DECLARE @Ds_Database [VARCHAR](50) = NULL, @Ds_Usuario [VARCHAR](50) = NULL, @Fl_Objetos_Sistema BIT = 0, @Fl_Permissoes_Instancia BIT = 1, @Fl_Permissoes_Banco BIT = 1;
    DECLARE @query VARCHAR(MAX);


    SET @query = '
    SELECT DISTINCT
        ' + CHAR( 39 ) + '?' + CHAR( 39 ) + ' [Database],
        C.name [Schema],
        COALESCE(B.name, E.name) [Object],
        COALESCE(B.type_desc, E.type_desc) AS [object_type],
        D.name username,
        A.type permissions_type,
        A.permission_name,
        A.state permission_state,
        A.state_desc,
        (CASE WHEN B.is_ms_shipped = 1 OR E.[object_id] IS NOT NULL OR B.name IN (''sysdiagrams'', ''sp_upgraddiagrams'', ''sp_helpdiagrams'', ''sp_helpdiagramdefinition'', ''sp_creatediagram'', ''sp_renamediagram'', ''sp_alterdiagram'', ''sp_dropdiagram'', ''fn_diagramobjects'') THEN 1 ELSE 0 END) AS [system_object],
        (CASE WHEN A.class = 1
            THEN ' + CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS
            ELSE ' + +CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS
        END) AS remover,
        (CASE WHEN A.class = 1
            THEN ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS 
            ELSE ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS
        END) AS conceder
    FROM
        [?].sys.database_permissions  A WITH(NOLOCK)
        LEFT JOIN [?].sys.objects    B WITH(NOLOCK) ON A.major_id = B.object_id
        LEFT JOIN [?].sys.schemas    C WITH(NOLOCK) ON B.schema_id = C.schema_id
        JOIN [?].sys.database_principals  D WITH(NOLOCK) ON A.grantee_principal_id = D.principal_id
        LEFT JOIN [?].sys.system_objects  E WITH(NOLOCK) ON A.major_id = E.object_id
    WHERE
        ' + ( CASE WHEN @Ds_Usuario IS NULL THEN '1 = 1' ELSE 'D.name LIKE ''' + ISNULL( @Ds_Usuario, '' ) + '''' END ) + '
        AND (
            (COALESCE(B.name, E.name) IS NOT NULL AND A.class = 1)
            OR (A.class NOT IN (1, 6))
        )
    ORDER BY
        1, 2, 3, 5';


    PRINT @query;


    DECLARE @Object_Permissions TABLE (
        [database]         sysname,
        [schema]           sysname     NULL,
        [object]           sysname     NULL,
        [object_type]      sysname     NULL,
        [username]         sysname,
        [permission_type]  sysname,
        [permission_name]  sysname,
        [permission_state] sysname,
        [state_desc]       sysname,
        [system_object]    BIT,
        [remover]          VARCHAR(MAX),
        [conceder]         VARCHAR(MAX)
    );


    IF ( @Fl_Permissoes_Banco = 1 )
    BEGIN
        INSERT INTO @Object_Permissions 
        EXEC master.dbo.sp_MSforeachdb @query;
    END;


    -- Roles
    DECLARE @Role_Permissions TABLE (
        DBName            sysname,
        UserName          sysname,
        LoginType         sysname,
        DefaultUser       BIT,
        AssociatedRole    VARCHAR(MAX),
        create_date       DATETIME,
        modify_date       DATETIME,
        grant_permission  VARCHAR(MAX),
        revoke_permission VARCHAR(MAX)
    );


    IF ( @Fl_Permissoes_Banco = 1 )
    BEGIN

        INSERT @Role_Permissions
        EXEC master.dbo.sp_MSforeachdb'
    SELECT DISTINCT
        ''?'' AS DB_Name,
        prin.name AS UserName,
        prin.type_desc AS LoginType,
        (CASE WHEN prin.principal_id < 5 THEN 1 ELSE 0 END) AS default_user,
        role.[name] AS AssociatedRole,
        prin.create_date,
        prin.modify_date,
        ''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] ADD MEMBER ['' + prin.[name] + ''];'' AS grant_permission,
        ''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] DROP MEMBER ['' + prin.[name] + ''];'' AS revoke_permission
    FROM 
        [?].sys.database_principals                 prin    WITH(NOLOCK)
        JOIN [?].sys.database_role_members    mem     WITH(NOLOCK) ON prin.principal_id = mem.member_principal_id
        JOIN [?].sys.database_principals    role  WITH(NOLOCK) ON mem.role_principal_id = role.principal_id
    WHERE 
        prin.sid IS NOT NULL 
        AND prin.principal_id > 4
        AND prin.is_fixed_role <> 1
        AND prin.name NOT LIKE ''##%''
    '   ;
    END;


    DECLARE @Tabela_Final TABLE (
        [database]        NVARCHAR(128),
        [schema]          NVARCHAR(128),
        [object]          NVARCHAR(128),
        [permission_type] VARCHAR(19),
        [system_object]   BIT,
        [username]        NVARCHAR(128),
        [object_type]     NVARCHAR(128),
        [permission_name] NVARCHAR(MAX),
        [read_only]       BIT,
        [state_desc]      NVARCHAR(128),
        [remover]         VARCHAR(MAX),
        [conceder]        VARCHAR(MAX)
    );


    IF ( @Fl_Permissoes_Banco = 1 )
    BEGIN

        INSERT INTO @Tabela_Final
        SELECT DISTINCT
            [database],
            [schema],
            [object],
            ( CASE WHEN object_type IS NULL THEN 'DATABASE_PERMISSION' ELSE 'DATABASE_OBJECT' END ) AS [permission_type],
            system_object,
            username,
            object_type,
            [permission_name],
            ( CASE
                  WHEN object_type = 'SQL_SCALAR_FUNCTION'
                       OR [permission_name] LIKE 'VIEW %'
                       OR [permission_name] IN ( 'SELECT', 'CONNECT', 'REFERENCES', 'SHOWPLAN' ) THEN 1
                  ELSE 0
              END
            )                                                                                       AS [read_only],
            state_desc,
            remover,
            conceder
        FROM
            @Object_Permissions
        WHERE
            (
                [username] LIKE @Ds_Usuario
                OR @Ds_Usuario IS NULL
            )
            AND
            (
                [database] = @Ds_Database
                OR @Ds_Database IS NULL
            )
            AND [username] <> 'dbo'
            AND
            (
                (
                    @Fl_Objetos_Sistema = 0
                    AND system_object = 0
                )
                OR @Fl_Objetos_Sistema = 1
            )

        UNION ALL

        SELECT DISTINCT
            DBName                                                                                                          AS [database],
            NULL                                                                                                            AS [schema],
            NULL                                                                                                            AS [object],
            'DATABASE_ROLE'                                                                                                 AS [object_type],
            DefaultUser                                                                                                     AS system_object,
            UserName                                                                                                        AS username,
            LoginType                                                                                                       AS permission_type,
            ISNULL( NULLIF(AssociatedRole, ''), 'public' )                                                                  AS [permission_name],
            ( CASE WHEN AssociatedRole IN ( 'db_datareader', 'SQLAgentUserRole', 'SQLAgentReaderRole' ) THEN 1 ELSE 0 END ) AS [read_only],
            'GRANT'                                                                                                         AS [state_desc],
            revoke_permission                                                                                               AS remover,
            grant_permission                                                                                                AS conceder
        FROM
            @Role_Permissions
        WHERE
            (
                [UserName] LIKE @Ds_Usuario
                OR @Ds_Usuario IS NULL
            )
            AND
            (
                [DBName] = @Ds_Database
                OR @Ds_Database IS NULL
            )
            AND [UserName] <> 'dbo'
            AND
            (
                (
                    @Fl_Objetos_Sistema = 0
                    AND DefaultUser = 0
                )
                OR @Fl_Objetos_Sistema = 1
            );
    END;


    IF ( @Fl_Permissoes_Instancia = 1 )
    BEGIN

        INSERT INTO @Tabela_Final
        SELECT DISTINCT
            NULL                                                                                                             AS [DB_Name],
            NULL                                                                                                             AS [schema],
            NULL                                                                                                             AS [object],
            'SERVER_PERMISSION'                                                                                              AS [permission_type],
            ( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END )                                                              AS system_object,
            A.[name]                                                                                                         AS [username],
            A.[type_desc]                                                                                                    AS [object_type],
            B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI                                                         AS [permission_name],
            ( CASE WHEN B.[permission_name] IN ( 'CONNECT SQL' ) OR B.[permission_name] LIKE 'VIEW %' THEN 1 ELSE 0 END )    AS [read_only],
            B.state_desc,
            'USE [master]; REVOKE ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' FROM [' + A.[name] + '];' AS revoke_permission,
            'USE [master]; GRANT ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' TO [' + A.[name] + '];'    AS grant_permission
        FROM
            sys.server_principals       A WITH ( NOLOCK )
            JOIN sys.server_permissions B WITH ( NOLOCK ) ON A.principal_id = B.grantee_principal_id
        WHERE
            A.[sid] IS NOT NULL
            AND A.is_disabled = 0
            AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN
            AND A.[name] NOT LIKE 'NT SERVICE\%'
            AND A.[name] NOT LIKE 'NT AUTHORITY\%'
            AND A.[name] NOT LIKE 'BUILTIN\%'
            AND
            (
                A.[name] LIKE @Ds_Usuario
                OR @Ds_Usuario IS NULL
            )

        UNION ALL

        SELECT DISTINCT
            NULL                                                                                 AS [DB_Name],
            NULL                                                                                 AS [schema],
            NULL                                                                                 AS [object],
            'SERVER_ROLE'                                                                        AS [permission_type],
            ( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END )                                  AS system_object,
            A.[name]                                                                             AS UserName,
            A.[type_desc]                                                                        AS LoginType,
            C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI                                        AS AssociatedRole,
            0                                                                                    AS [read_only],
            'GRANT'                                                                              AS [state_desc],
            'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] DROP MEMBER [' + A.[name] + '];' AS revoke_permission,
            'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] ADD MEMBER [' + A.[name] + '];'  AS grant_permission
        FROM
            sys.server_principals        A WITH ( NOLOCK )
            JOIN sys.server_role_members B WITH ( NOLOCK ) ON A.principal_id = B.member_principal_id
            JOIN sys.server_principals   C WITH ( NOLOCK ) ON B.role_principal_id = C.principal_id
        WHERE
            A.[sid] IS NOT NULL
            AND A.is_disabled = 0
            AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN
            AND A.[name] NOT LIKE 'NT SERVICE\%'
            AND A.[name] NOT LIKE 'NT AUTHORITY\%'
            AND A.[name] NOT LIKE 'BUILTIN\%'
            AND
            (
                A.[name] LIKE @Ds_Usuario
                OR @Ds_Usuario IS NULL
            );
    END;


    SELECT
        * 
    FROM 
        @Tabela_Final 
    ORDER BY 
        [database], 
        username, 
        [schema], 
        [object];


END

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

IF (OBJECT_ID('dbo.stpVerifica_Permissoes_Usuario') IS NULL) EXEC('CREATE PROCEDURE dbo.stpVerifica_Permissoes_Usuario AS SELECT 1')

ALTER PROCEDURE [dbo].[stpVerifica_Permissoes_Usuario]

@Ds_Database [varchar](50) = NULL,

@Ds_Usuario [varchar](50) = NULL,

@Fl_Objetos_Sistema BIT = 0,

@Fl_Permissoes_Instancia BIT = 1,

@Fl_Permissoes_Banco BIT = 1

BEGIN

-- DECLARE @Ds_Database [VARCHAR](50) = NULL, @Ds_Usuario [VARCHAR](50) = NULL, @Fl_Objetos_Sistema BIT = 0, @Fl_Permissoes_Instancia BIT = 1, @Fl_Permissoes_Banco BIT = 1;

DECLARE @query VARCHAR(MAX);

SET @query = '

SELECT DISTINCT

' + CHAR( 39 ) + '?' + CHAR( 39 ) + ' [Database],

C.name [Schema],

COALESCE(B.name, E.name) [Object],

COALESCE(B.type_desc, E.type_desc) AS [object_type],

D.name username,

A.type permissions_type,

A.permission_name,

A.state permission_state,

A.state_desc,

(CASE WHEN B.is_ms_shipped = 1 OR E.[object_id] IS NOT NULL OR B.name IN (''sysdiagrams'', ''sp_upgraddiagrams'', ''sp_helpdiagrams'', ''sp_helpdiagramdefinition'', ''sp_creatediagram'', ''sp_renamediagram'', ''sp_alterdiagram'', ''sp_dropdiagram'', ''fn_diagramobjects'') THEN 1 ELSE 0 END) AS [system_object],

(CASE WHEN A.class = 1

THEN ' + CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS

ELSE ' + +CHAR( 39 ) + 'USE [?]; REVOKE ' + CHAR( 39 ) + ' + A.permission_name + ' + CHAR( 39 ) + ' FROM [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS

END) AS remover,

(CASE WHEN A.class = 1

THEN ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' ON [' + CHAR( 39 ) + ' + C.name + ' + CHAR( 39 ) + '].[' + CHAR( 39 ) + ' + COALESCE(B.name, C.name) + ' + CHAR( 39 ) + '] TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS

ELSE ' + CHAR( 39 ) + 'USE [?]; ' + CHAR( 39 ) + ' + A.state_desc + '' '' + A.permission_name + ' + CHAR( 39 ) + ' TO [' + CHAR( 39 ) + ' + D.name + ' + CHAR( 39 ) + '];' + CHAR( 39 ) + ' COLLATE LATIN1_General_CI_AS

END) AS conceder

FROM

[?].sys.database_permissions A WITH(NOLOCK)

LEFT JOIN [?].sys.objects B WITH(NOLOCK) ON A.major_id = B.object_id

LEFT JOIN [?].sys.schemas C WITH(NOLOCK) ON B.schema_id = C.schema_id

JOIN [?].sys.database_principals D WITH(NOLOCK) ON A.grantee_principal_id = D.principal_id

LEFT JOIN [?].sys.system_objects E WITH(NOLOCK) ON A.major_id = E.object_id

WHERE

' + ( CASE WHEN @Ds_Usuario IS NULL THEN '1 = 1' ELSE 'D.name LIKE ''' + ISNULL( @Ds_Usuario, '' ) + '''' END ) + '

AND (

(COALESCE(B.name, E.name) IS NOT NULL AND A.class = 1)

OR (A.class NOT IN (1, 6))

)

ORDER BY

1, 2, 3, 5';

PRINT @query;

DECLARE @Object_Permissions TABLE (

[database] sysname,

[schema] sysname NULL,

[object] sysname NULL,

[object_type] sysname NULL,

[username] sysname,

[permission_type] sysname,

[permission_name] sysname,

[permission_state] sysname,

[state_desc] sysname,

[system_object] BIT,

[remover] VARCHAR(MAX),

[conceder] VARCHAR(MAX)

);

IF ( @Fl_Permissoes_Banco = 1 )

BEGIN

INSERT INTO @Object_Permissions

EXEC master.dbo.sp_MSforeachdb @query;

END;

-- Roles

DECLARE @Role_Permissions TABLE (

DBName sysname,

UserName sysname,

LoginType sysname,

DefaultUser BIT,

AssociatedRole VARCHAR(MAX),

create_date DATETIME,

modify_date DATETIME,

grant_permission VARCHAR(MAX),

revoke_permission VARCHAR(MAX)

);

IF ( @Fl_Permissoes_Banco = 1 )

BEGIN

INSERT @Role_Permissions

EXEC master.dbo.sp_MSforeachdb'

SELECT DISTINCT

''?'' AS DB_Name,

prin.name AS UserName,

prin.type_desc AS LoginType,

(CASE WHEN prin.principal_id < 5 THEN 1 ELSE 0 END) AS default_user,

role.[name] AS AssociatedRole,

prin.create_date,

prin.modify_date,

''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] ADD MEMBER ['' + prin.[name] + ''];'' AS grant_permission,

''USE ['' + ''?'' + '']; ALTER ROLE ['' + role.[name] + ''] DROP MEMBER ['' + prin.[name] + ''];'' AS revoke_permission

FROM

[?].sys.database_principals prin WITH(NOLOCK)

JOIN [?].sys.database_role_members mem WITH(NOLOCK) ON prin.principal_id = mem.member_principal_id

JOIN [?].sys.database_principals role WITH(NOLOCK) ON mem.role_principal_id = role.principal_id

WHERE

prin.sid IS NOT NULL

AND prin.principal_id > 4

AND prin.is_fixed_role <> 1

AND prin.name NOT LIKE ''##%''

' ;

END;

DECLARE @Tabela_Final TABLE (

[database] NVARCHAR(128),

[schema] NVARCHAR(128),

[object] NVARCHAR(128),

[permission_type] VARCHAR(19),

[system_object] BIT,

[username] NVARCHAR(128),

[object_type] NVARCHAR(128),

[permission_name] NVARCHAR(MAX),

[read_only] BIT,

[state_desc] NVARCHAR(128),

[remover] VARCHAR(MAX),

[conceder] VARCHAR(MAX)

);

IF ( @Fl_Permissoes_Banco = 1 )

BEGIN

INSERT INTO @Tabela_Final

SELECT DISTINCT

[database],

[schema],

[object],

( CASE WHEN object_type IS NULL THEN 'DATABASE_PERMISSION' ELSE 'DATABASE_OBJECT' END ) AS [permission_type],

system_object,

username,

object_type,

[permission_name],

( CASE

WHEN object_type = 'SQL_SCALAR_FUNCTION'

OR [permission_name] LIKE 'VIEW %'

OR [permission_name] IN ( 'SELECT', 'CONNECT', 'REFERENCES', 'SHOWPLAN' ) THEN 1

ELSE 0

END

) AS [read_only],

state_desc,

remover,

conceder

FROM

@Object_Permissions

WHERE

(

[username] LIKE @Ds_Usuario

OR @Ds_Usuario IS NULL

)

AND

(

[database] = @Ds_Database

OR @Ds_Database IS NULL

)

AND [username] <> 'dbo'

AND

(

@Fl_Objetos_Sistema = 0

AND system_object = 0

)

OR @Fl_Objetos_Sistema = 1

)

UNION ALL

SELECT DISTINCT

DBName AS [database],

NULL AS [schema],

NULL AS [object],

'DATABASE_ROLE' AS [object_type],

DefaultUser AS system_object,

UserName AS username,

LoginType AS permission_type,

ISNULL( NULLIF(AssociatedRole, ''), 'public' ) AS [permission_name],

( CASE WHEN AssociatedRole IN ( 'db_datareader', 'SQLAgentUserRole', 'SQLAgentReaderRole' ) THEN 1 ELSE 0 END ) AS [read_only],

'GRANT' AS [state_desc],

revoke_permission AS remover,

grant_permission AS conceder

FROM

@Role_Permissions

WHERE

(

[UserName] LIKE @Ds_Usuario

OR @Ds_Usuario IS NULL

)

AND

(

[DBName] = @Ds_Database

OR @Ds_Database IS NULL

)

AND [UserName] <> 'dbo'

AND

(

@Fl_Objetos_Sistema = 0

AND DefaultUser = 0

)

OR @Fl_Objetos_Sistema = 1

);

END;

IF ( @Fl_Permissoes_Instancia = 1 )

BEGIN

INSERT INTO @Tabela_Final

SELECT DISTINCT

NULL AS [DB_Name],

NULL AS [schema],

NULL AS [object],

'SERVER_PERMISSION' AS [permission_type],

( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END ) AS system_object,

A.[name] AS [username],

A.[type_desc] AS [object_type],

B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI AS [permission_name],

( CASE WHEN B.[permission_name] IN ( 'CONNECT SQL' ) OR B.[permission_name] LIKE 'VIEW %' THEN 1 ELSE 0 END ) AS [read_only],

B.state_desc,

'USE [master]; REVOKE ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' FROM [' + A.[name] + '];' AS revoke_permission,

'USE [master]; GRANT ' + B.[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AI + ' TO [' + A.[name] + '];' AS grant_permission

FROM

sys.server_principals A WITH ( NOLOCK )

JOIN sys.server_permissions B WITH ( NOLOCK ) ON A.principal_id = B.grantee_principal_id

WHERE

A.[sid] IS NOT NULL

AND A.is_disabled = 0

AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN

AND A.[name] NOT LIKE 'NT SERVICE\%'

AND A.[name] NOT LIKE 'NT AUTHORITY\%'

AND A.[name] NOT LIKE 'BUILTIN\%'

AND

(

A.[name] LIKE @Ds_Usuario

OR @Ds_Usuario IS NULL

)

UNION ALL

SELECT DISTINCT

NULL AS [DB_Name],

NULL AS [schema],

NULL AS [object],

'SERVER_ROLE' AS [permission_type],

( CASE WHEN A.principal_id < 10 THEN 1 ELSE 0 END ) AS system_object,

A.[name] AS UserName,

A.[type_desc] AS LoginType,

C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI AS AssociatedRole,

0 AS [read_only],

'GRANT' AS [state_desc],

'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] DROP MEMBER [' + A.[name] + '];' AS revoke_permission,

'USE [master]; ALTER SERVER ROLE [' + C.[name] + '] ADD MEMBER [' + A.[name] + '];' AS grant_permission

FROM

sys.server_principals A WITH ( NOLOCK )

JOIN sys.server_role_members B WITH ( NOLOCK ) ON A.principal_id = B.member_principal_id

JOIN sys.server_principals C WITH ( NOLOCK ) ON B.role_principal_id = C.principal_id

WHERE

A.[sid] IS NOT NULL

AND A.is_disabled = 0

AND A.[type] <> 'C' -- CERTIFICATE_MAPPED_LOGIN

AND A.[name] NOT LIKE 'NT SERVICE\%'

AND A.[name] NOT LIKE 'NT AUTHORITY\%'

AND A.[name] NOT LIKE 'BUILTIN\%'

AND

(

A.[name] LIKE @Ds_Usuario

OR @Ds_Usuario IS NULL

);

END;

SELECT

FROM

@Tabela_Final

ORDER BY

[database],

username,

[schema],

[object];

END

Examples of use:

-- Verifica as permissões de todos os usuários do database "dirceuresende"
EXEC dbo.stpVerifica_Permissoes_Usuario
    @Ds_Database = 'dirceuresende', -- varchar(50)
    @Ds_Usuario = '', -- varchar(50)
    @Fl_Objetos_Sistema = 0, -- bit
    @Fl_Permissoes_Instancia = 0, -- bit
    @Fl_Permissoes_Banco = 1 -- bit

-- Verifica as permissões de todos os usuários do database "dirceuresende"

EXEC dbo.stpVerifica_Permissoes_Usuario

@Ds_Database = 'dirceuresende', -- varchar(50)

@Ds_Usuario = '', -- varchar(50)

@Fl_Objetos_Sistema = 0, -- bit

@Fl_Permissoes_Instancia = 0, -- bit

@Fl_Permissoes_Banco = 1 -- bit

Result:

-- Verifica as permissões do usuário "teste" em todos os databases
EXEC dbo.stpVerifica_Permissoes_Usuario
    @Ds_Database = NULL, -- varchar(50)
    @Ds_Usuario = 'teste', -- varchar(50)
    @Fl_Objetos_Sistema = 0, -- bit
    @Fl_Permissoes_Instancia = 0, -- bit
    @Fl_Permissoes_Banco = 1 -- bit

-- Verifica as permissões do usuário "teste" em todos os databases

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

Checking a user's permissions in SQL Server

Using system SP, sp_helprotect

Using Transact SQL Queries in Catalog Views

Using a Custom Stored Procedure

Using a Stored Procedure with GRANT and REVOKE Code