Grant Permissions Through Coding

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Grant Permissions Through Coding

Author

Topic

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 05:08:56

Hello

Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.

Is it possible to grant permissions without specifying the username,passwd

Thanks In Advance
Poornima

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 05:21:53

Yes you can definitely give permissions through code.

quote:
without specifying the username,passwd

Without specifying username? Then to whom you want to give permission?

Harsh Athalye
India.
"Nothing is Impossible"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 05:38:58

Somthing like this?


Grant Select, Insert,Update 
On Table To Public

Chirag

http://chirikworld.blogspot.com/

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 05:42:10

I asked without specifying the user coz ,actually i have an application that retrives the table frm a db from SQLExpress an access this appln from a webservice remotely.In the client side ,iam going to install SQLExpress and not SQL Server 2005.
In SQLExpress there is no UserInterface like in SQLServer Management Studio to create users & grant permissions.

So now is there a way to also create the users dynamically
Poornima

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-14 : 05:45:29

"So now is there a way to also create the users dynamically"
Yes. Do it the T-SQL way

Look for system stored procedure in Books On Line.

sp_addlogin

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 05:52:40

Hi Chirag
Sorry the SQL Query you mentioned did not work ,i.e the permisions were not granted.Plz tell me is there any other way

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 05:56:47

Try this:

GRANT SELECT ON OBJECT::SCHEMA.TABLE
TO USER

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 06:01:02

quote:
actually i have an application that retrives the table frm a db from SQLExpress an access this appln from a webservice remotely

How your application connects to SQLExpress? There surely must be some authentication information in the connection string.

quote:
In SQLExpress there is no UserInterface like in SQLServer Management Studio to create users & grant permissions.

Yes, that true!
Because SQLExpress is just the database engine and it can be connected either through SQLCMD utility or Management Studio. But that does not mean the users can't be created or permissions can't be given. Everything which can be done through GUI tools like Management Studio can be done more efficiently through SQL statements.

Harsh Athalye
India.
"Nothing is Impossible"

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 06:01:57

Thanks Harsh

The query i executed is
sQuery = GRANT SELECT ON Object ::SCHEMA.TAble TO PUBLIC
But it is giving error as

"Incorrect Syntax near the Keyword SCHEMA

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-11-14 : 06:02:50

In SQLExpress there is no UserInterface like in SQLServer Management Studio to create users & grant permissions

I think there is a SSMS, i remember when i had intalled on my notebook i got the SSMS. you verify it before you actually start implementing it.

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 06:17:44

quote:
Incorrect Syntax near the Keyword SCHEMA

~~Chirag~~Poornima,

You will have to replace your respective schema name, for example, HumanResources and Table Name as per your context. It's not keyword.

My mistake..it's should be read this way:

GRANT SELECT ON OBJECT::<Schema_Name>.<Table_Name>
TO <User_Name>

Hope now it is more clear!

Harsh Athalye
India.
"Nothing is Impossible"

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 06:48:44

Thank you
as iam new to SQL i could catch you

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 06:54:29

Can any one of you tell me how to grant permissions to the entire database through coding

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 06:58:06

[code]GRANT CREATE TABLE TO <User_name>[/code]

Also read:
[url]http://msdn2.microsoft.com/en-us/library/ms187965.aspx[/url]

Harsh Athalye
India.
"Nothing is Impossible"

poornima
Starting Member

32 Posts

Posted - 2006-11-14 : 07:01:57

what you have specified is only create table permission .
How to grant INSERT,UPDATE,SELECT,DELETE permissions to all the tables in a particular database

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-14 : 07:11:25

For that, you can create schema and transfer all those tables to the newly created schema whom you want to give permissions and then give permissions to Schema.

GRANT SELECT ON <Schema_Name> To <User_Name>

Harsh Athalye
India.
"Nothing is Impossible"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-11-14 : 10:40:46

quote:
Originally posted by poornima

what you have specified is only create table permission .
How to grant INSERT,UPDATE,SELECT,DELETE permissions to all the tables in a particular database

For such a horrible design where EVERYONE needs ALL permissions, just have your application connect to the DB as SA and have done with it!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources