SQL Server如何去掉某个用户的select * 权限

 一般的DBA都不希望开发人员或者用户在查询中使用select * ... 这个命令,下面就介绍一个可以让一般的用户无法使用Select * from命令的方法。

 

首先创建一个账号和一个数据库并添加这个用户为这个数据库的使用者。

USE MASTERGO CREATE LOGIN test_user WITH PASSWORD = 'test_user_pwd';GO CREATE DATABASE DenySelectStar;GO USE DenySelectStarGO CREATE USER test_user FROM LOGIN test_user WITH DEFAULT_SCHEMA = DenySelectStar;GO EXEC sp_addrolemember N'db_datareader', N'test_user'GO   

第二部创建一个新表，并命名一列为"dummycolumn"。并插入几条数据:

 CREATE TABLE dbo.Table_1( IdentityKey INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,ColumnOne INT NULL ,DummyColumn CHAR(1) NULL);GO INSERT INTO dbo.Table_1 (ColumnOne) SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5;GO DENY SELECT ON OBJECT:: dbo.Table_1(DummyColumn) TO test_user;GO

现在再去查询一下select * from :

 USE DenySelectStarGO SELECT * FROM dbo.Table_1; --Result Msg 230, Level 14, State 1, Line 1The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.

试试一个不是select *的查询命令:

 USE DenySelectStarGO SELECT IdentityKey, ColumnOne FROM dbo.Table_1; --Result IdentityKey ColumnOne----------- -----------1 12 23 34 45 5 (5 row(s) affected)

再来几个其他的测试:

 USE DenySelectStarGO SELECT COUNT(*) FROM dbo.Table_1;SELECT COUNT(1) FROM dbo.Table_1; --Result Msg 230, Level 14, State 1, Line 1The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.Msg 230, Level 14, State 1, Line 2The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'. USE DenySelectStarGO SELECT COUNT(IdentityKey) As TotalRows FROM dbo.Table_1; --Result TotalRows-----------5 (1 row(s) affected) USE DenySelectStarGO IF EXISTS (SELECT * FROM dbo.Table_1) PRINT 'Has Rows'IF EXISTS (SELECT 1 FROM dbo.Table_1) PRINT 'Has Rows' --Result Msg 230, Level 14, State 1, Line 1The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'.Msg 230, Level 14, State 1, Line 3The SELECT permission was denied on the column 'DummyColumn' of the object 'Table_1", database 'DenySelectStar', schema 'dbo'. USE DenySelectStarGO IF EXISTS (SELECT IdentityKey FROM dbo.Table_1) PRINT 'Has Rows' --Result Has Rows

 

不错吧。希望对你有帮助。

最后是删除所有测试的资源:

 USE MASTER GO DROP DATABASE DenySelectStar; GO DROP LOGIN test_user; GO

http://www.sqlservercentral.com/articles/SELECT+*/68324/