Adding Roles/Users using SQL Procedures...

Hi everyone out there in ASP.NET land.

Have a quick question...

How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?

Thanks ahead of time,
DenvasCheck in this link a couple of PageDowns in the sectionCreate a New Login

The Sql syntax for adding a role member:

EXEC sp_addrolemember N'db_owner', N'SomeUserName'

|||Thank you so much for the info. Going to apply it tomorrow.

-Denvas

Adding Permissions

We created a database with approximately 80 tables and 170 stored procedures
.
We were not too bright to begin with and one thing we did not consider was
not using sa as the login. Now, we need to create a user that does not have
full sa privileges but can execute all stored procedures and view all data i
n
the tables. Some of the stored procedures use dynamic sql (sp_executesql). W
e
also will be creating a new database, which corresponds to a specific test
event, about two or three times a month.
Question I have is can we create one user that can access all the stored
procedures and tables in ALL databases (as they are created) and is there a
fast way of granting privileges to all these objects without having to go in
the permissions for each one?You can create a login and create a user which is in the data_reader role in
every database and grant it rights to execute all stored procedures.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
> We created a database with approximately 80 tables and 170 stored
> procedures.
> We were not too bright to begin with and one thing we did not consider was
> not using sa as the login. Now, we need to create a user that does not
> have
> full sa privileges but can execute all stored procedures and view all data
> in
> the tables. Some of the stored procedures use dynamic sql (sp_executesql).
> We
> also will be creating a new database, which corresponds to a specific test
> event, about two or three times a month.
> Question I have is can we create one user that can access all the stored
> procedures and tables in ALL databases (as they are created) and is there
> a
> fast way of granting privileges to all these objects without having to go
> in
> the permissions for each one?
>|||Also note that in SQL Server 2005, you can grant SELECT and EXECUTE
permissions at database level, so you don't need to perform grants for each
table or procedure.
Do you need to do this in all databases or only in databases of a certain
type? If the answer is all, consider setting this up in the model database.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:up6oLty7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> You can create a login and create a user which is in the data_reader role
> in every database and grant it rights to execute all stored procedures.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Wannabe" <Wannabe@.discussions.microsoft.com> wrote in message
> news:FE43D341-3AD0-459D-AA6A-794CA3E02915@.microsoft.com...
>

Adding extra parameter to my report doesn't work.

I have a problem in RS 2000 where when I add another parameter to my
current two it doesn't work. I've tested the procedures in SQL and
when declaring the datasets. The two parameters I have are:
"Report_Date" and "Project_Name". The one I add is called "Cap_Num".
I've modified all the existing datasets to use this new parameter in
addition to the others, but when I preview the report, nothing happens
when I select a value from "Report_Date" and "Cap_Num". I only get
results when I choose the "Project_Name" value and "Report_Date". On
the web when I run the report and I try to select just based on the
"Cap_Num" and "Report_Date" it says to choose a "Project_Name". I've
gone through all of my datasets and queries and have put the proper
conditions for each:
'where xxxx = @.Report_Date and @.Project_Name OR xxxx=@.Report_Date and
@.Cap_Num'
Like I said I tested all of the parameters by themselves and the work,
but when they are all integrated, it is still looking for the project
name to be selected. Any ideas?Hi,
I've had a few problems when adding parameters in the past myself.
Couple of things I would check are:
1. Go into the layout tab for the report, select Report Menu and go
into Report Parameters. Make sure the parameters are added in here.
2. Go into the data tab for the report, select the ... button to edit
the dataset, click to the parameters tab and make sure your have an
entry here for your third param should be like
Name Value
CAP_NUM =Parameters!CAP_NUM.Value
hope this helps|||I find that I need to do "Refresh Fields" in the Data window frequently.
"siphonalfusion" <jamiewyant@.comcast.net> wrote in message
news:1146781767.843959.274910@.v46g2000cwv.googlegroups.com...
>I have a problem in RS 2000 where when I add another parameter to my
> current two it doesn't work. I've tested the procedures in SQL and
> when declaring the datasets. The two parameters I have are:
> "Report_Date" and "Project_Name". The one I add is called "Cap_Num".
> I've modified all the existing datasets to use this new parameter in
> addition to the others, but when I preview the report, nothing happens
> when I select a value from "Report_Date" and "Cap_Num". I only get
> results when I choose the "Project_Name" value and "Report_Date". On
> the web when I run the report and I try to select just based on the
> "Cap_Num" and "Report_Date" it says to choose a "Project_Name". I've
> gone through all of my datasets and queries and have put the proper
> conditions for each:
> 'where xxxx = @.Report_Date and @.Project_Name OR xxxx=@.Report_Date and
> @.Cap_Num'
> Like I said I tested all of the parameters by themselves and the work,
> but when they are all integrated, it is still looking for the project
> name to be selected. Any ideas?
>|||I agree you will also need to do as Bill says|||I actually found out that I needed to use default parameters. Now it
is working. Thank you all!

Adding Execute permissions on Stored Procedures

How can i add Execute permissions on the Stored Procedures under the
created user permission iusing SQLDMO ?hi,
arfanameer@.hotmail.com wrote:
> How can i add Execute permissions on the Stored Procedures under the
> created user permission iusing SQLDMO ?

Dim oProcedure As SQLDMO.StoredProcedure
Dim oServer As SQLDMO.SQLServer
' connect to the server, etc...

'get a pointer to the procedure..
Set oProcedure = oServer.Databases("Pubs").StoredProcedures("byroyalty")
'grant EXEC permission to an existing user
oProcedure.Grant SQLDMOPriv_Execute, "user_name"

Set oProcedure = Nothing
Set oServer = Nothing

--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--- remove DMO to reply

Adding execute permission to stored procedures

Hi

I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2.

For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure.

In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user.

Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this.

If anyone can point me in the right direction on this...

I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005?

Thanking you in advance

A db owner can execute, alternatively you could add server roles to the login such as sysadmin may be overkill though

Function SetLoginsDbOwner()Dim strcmdAsString

strcmd = "exec sp_addrolemember N'db_owner', N'MyLoginName_IWantSPExecutePerm'"

Dim dcAsNew OleDbConnection(connstringL)Dim cmdAsNew OleDbCommand(strcmd, dc)Try

dc.Open()

cmd.ExecuteNonQuery()

Catch exAs OleDbException

Label3.Text = ex.ToString()

Finally

dc.Close()

EndTryEndFunction

Adding Delete to Stored Procedures

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out

REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN

GOI dont believe that a simple answer like
DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
is what you are looking at. Can u please give us some more details on what is expected?|||From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:


declare @.err int
BEGIN TRAN
INSERT INTO TableB (col1, col2, col3, etc)
SELECT col1, col2, col3, etc)
FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

DELETE FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

COMMIT|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

ALTER PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
IF @.@.Error <> '0'

RETURN

GO|||inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.