SQL Server 2008:如何向用户名授予权限? [英] SQL Server 2008: how do I grant privileges to a username?

问题描述

我需要能够通过 SQL Server 身份验证建立 ODBC 连接.

在 SSMS 中，我如何授予用户在特定数据库上拥有所有权利的权限?

有没有办法用 SSMS 以图形方式执行此操作?

解决方案

如果你想给你的用户所有的读取权限，你可以使用:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'

这会向该用户添加默认的 db_datareader 角色(对所有表的读取权限).

还有一个 db_datawriter 角色 - 它为您的用户提供对所有表的所有写入权限(插入、更新、删除):

EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

如果需要更细化，可以使用GRANT命令:

GRANT SELECT, INSERT, UPDATE ON dbo.YourTable TO YourUserName授予选择，插入 dbo.YourTable2 到 YourUserName授予选择，删除 dbo.YourTable3 到 YourUserName

等等 - 您可以精细地授予对特定表的 SELECT、INSERT、UPDATE、DELETE 权限.

用于 SQL Server 的 MSDN 联机丛书中详细记录了这一切.>

是的，您也可以以图形方式进行操作 - 在 SSMS 中，转到您的数据库，然后 Security >用户，右键单击您要授予权限的用户，然后在底部的 Properties 和数据库角色成员资格"中，您可以将用户添加到数据库角色.

I need to be able to establish an ODBC connection through SQL Server authentication.

In SSMS how do I grant permission for a user to be able to have ALL RIGHTS on a specific database?

is there a way to do this graphically with SSMS?

解决方案

If you want to give your user all read permissions, you could use:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'

That adds the default db_datareader role (read permission on all tables) to that user.

There's also a db_datawriter role - which gives your user all WRITE permissions (INSERT, UPDATE, DELETE) on all tables:

EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

If you need to be more granular, you can use the GRANT command:

GRANT SELECT, INSERT, UPDATE ON dbo.YourTable TO YourUserName
GRANT SELECT, INSERT ON dbo.YourTable2 TO YourUserName
GRANT SELECT, DELETE ON dbo.YourTable3 TO YourUserName

and so forth - you can granularly give SELECT, INSERT, UPDATE, DELETE permission on specific tables.

This is all very well documented in the MSDN Books Online for SQL Server.

And yes, you can also do it graphically - in SSMS, go to your database, then Security > Users, right-click on that user you want to give permissions to, then Properties adn at the bottom you see "Database role memberships" where you can add the user to db roles.

这篇关于SQL Server 2008:如何向用户名授予权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！