SQL Server 2008: How to Grant Privileges to a Username

SQL Server 2008: how do I grant privileges to a username?

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.

T-SQL How to grant role to user with ADMIN/GRANT option in SQL Server 2008

The stored procedure sp_addrolemember has 2(two) parameters only:

sp_addrolemember [ @rolename = ] 'role', [ @membername = ] 'security_account'

WITH GRANT OPTION has nothing to do with sp_addrolemember, instead it is the option of GRANT.
edited:
If you want your role members to be able to grant their permissions to others, you should grant these permissions to the role with the grant option.
When the user has to grant a permission to some other user, he must use the additional AS clause like this:

  grant select on myTbl to AnotherUser as MyRole

Granting Full SQL Server Permissions for a Database

If you literally want them to be able to do anything in that database, you can just add them to the db_owner role:

USE ContainedDatabase;
GO
ALTER ROLE db_owner ADD MEMBER [username];

If you want to be more granular, you can add them to lesser roles, like db_ddladmin, db_securityadmin, etc. You can see the list of built-in roles here:

• Database-Level Roles

The permissions inherent in each of those roles:

• Permissions of Fixed Database Roles

And if those don't suit, you can create your own roles, add your user to that role, and grant specific permissions to that role you created (and/or add them to other roles). The difference between applying the permissions to the role instead of directly to the user is simply reuse - if you add five more users that you want to apply the same permissions, you just add them to the custom role, rather than apply those granular permissions or roles to all 5 of the users.

SQL is it possible to grant permission to the view and not the user to access other db?

Your issue here is one of cross database ownership chains

You can make it work by following the instructions at http://support.microsoft.com/kb/810474

EXEC sp_configure 'Cross DB Ownership Chaining', '1'; RECONFIGURE 
EXEC sp_dboption 'YourDatabase', 'db chaining', 'true'

But be sure to read up about Cross Database Ownership Chaining and the associated risks.

http://msdn.microsoft.com/en-us/library/ms188676.aspx

How can I grant a user limited permissions in SQL Server 2008?

use DatabaseCatalogue
go    
exec sp_addrolemember db_datareader, StudentAssistantsUsername 
go

http://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

SQL Server 2008 R2 User Permissions

In the header of your stored procedure use WITH EXECUTE AS 'somePrincipal', where somePrincipal has the necessary permissions to access the symmetric key and certificate.

---

Related Topics