SP Error When Trying To Create User And Assign A Role
Aug 23, 2002
I get the following errors associated with trying to create an SP.
Server: Msg 170, Level 15, State 1, Procedure AddFortuneUser, Line 8
Line 8: Incorrect syntax near '@newuser'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@newuser'.
Can anyone explain why I have to do a declare.
I suspect I have to issue "declare @newuser sysname" somewhere but I'm not sure why.
The following is the code I'm trying to run.
My intent would be to create a form for the Admin Clerk that would call this SP. That way they can create a generic login. They have an application that allows them to change the password after the fact.
/*
Created for Admin person to allow them to add a basic SQL Login Account
forcing the user to be a member of a specific role 'helmsman'
in a specific database 'Fortune'
*/
CREATE PROCEDURE AddFortuneUser
@newuser char(128)
AS
EXEC master..sp_addlogin @loginame=@newuser, @passwd =substring(@newuser,1,8), @defdb =Fortune
GO
if not exists (select * from dbo.sysusers where name = @newuser and uid < 16382)
EXEC sp_grantdbaccess @loginame=@newuser, @name_in_db=@newuser
GO
exec master..sp_addrolemember @rolename ='helmsman', @membername =@newuser
GO
View 1 Replies
ADVERTISEMENT
Mar 25, 2008
Just wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
Any info would be helpful! TIA.
View 3 Replies
View Related
Jan 7, 2004
Hi,
I hv an application which is using ASP.net. The connectionstring in web.config is
<appSettings>
<add key = "constring" value = "Initial Catalog=mydatabase;Data Source=mypc-pc;User ID=User1; Password=password1"/>
</appSettings>"
Then, i hv created a user in SQL Server 2000 which is User1. What should i put for the database role? db_owner or just db_datareader and db_datawriter?
pls help.
Thnx
View 4 Replies
View Related
May 26, 2015
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies
View Related
Jun 13, 2007
Hi there,
How can I assign a user role of SQLAgentOperatorRole.
I tried with sp_addsrvrolemember but failed.
Thanks
Rahul
View 5 Replies
View Related
Feb 9, 2000
SQL Server 7
i did a restore of a database, then tried to add the User login
to it, but when i select database access, i get the followinf error :-
"Error 15023, user or role already exists !
the user did exist on the Database, but when i select Database,Users or
Database,Roles the User doesn't exist !! so i can't drop it !
any ideas ??
View 2 Replies
View Related
Sep 20, 2001
I used a backup copy of our production DB (residing in our prod machine) to do a database RESTORE to our test DB (residing in our test machine). This step was successful.
However when I tried to access the test DB via Peoplesoft application, I am unable to logon. Only then did I notice that all the users, with the exception of "sa", were gone.
When I attempted to add a user via Enterprise Manager's Action - Add Database User, I get the message, "Error 15023: User or role '%' already exists in the current database.
What's the best way to fix this without resorting to copying the source server's master database (If i do this, I risk clobbering some other DB's that are present in the target server but not in source server)?
Any help you can provide will be greatly appreciated!!!
View 2 Replies
View Related
Apr 27, 2015
I am trying the assign the models to the user in the user management page. But when I try to expand the + symbol next to the model and open the entities, I get the error that the object reference is not set for a object server instance.
View 2 Replies
View Related
Dec 27, 2006
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View 1 Replies
View Related
Jan 23, 2001
Hi everyone,
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution.
I first select the database names into a #temp table which has two columns, ID and dbname.
After that I use the following code:
DECLARE @Count smallint
declare @dbVarchar(20)
SET @Count = 1
WHILE (@Count <=(SELECT MAX(ID) FROM #temp))
BEGIN
Exec ("USE @db")
EXEC sp_adduser 'test'
EXEC sp_addrolemember 'my_role', 'testrole'
SET @COUNT = @COUNT + 1
SET @db = (SELECT dbname FROM #temp WHERE ID = @Count)
END
It seems that the "Use @db" part has no value for the variable @db.
Does anybody knows how to solve this?
View 1 Replies
View Related
May 8, 2008
Hi
I have add one new database mdf file in my project by ---> add new Item in to project.
But This database.mdf file is in windows authetication mode.
So there is no password assign to this.
I want to assign use id and password to this database or I want to give sql server authetication mode.
I have tried Modfy Connection property of database but that is not working. what is the default username of this conncetion?????
REply.....
View 3 Replies
View Related
Oct 29, 2006
im using the Northwind database, i use T-SQL commands to create a view that restricts users from seeing the address, city for all employees.
i dont know how to assign the view to a user in the database using Enterprise manager.
pls help me, im really needing a answer.
thanks!
View 5 Replies
View Related
Jan 30, 2006
I have just managed to have JDBC working, but I am getting an error that the user does not exist. I have read in the MSDN help that I need to set the sqlExpress to accept SQL server authentication and not windows authentication. So when I am trying to create a new User using Microsoft SQL server management studio express I get the following error.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Login 'adam'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15195&LinkId=20476
View 5 Replies
View Related
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
May 6, 2014
Only to a specific schema? Can this be done?
View 5 Replies
View Related
Sep 4, 2007
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char
View 3 Replies
View Related
Oct 25, 2000
Hi Everybody,
The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database.
My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles.
Can anyone guide me please.
thanks,
Srinivasan.
View 2 Replies
View Related
Dec 19, 2006
Hi:
In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.
MA2005
View 1 Replies
View Related
Feb 23, 2008
I have created the functionality to dynamically create databases and am now trying to figure out how to create database roles using T-SQL.
I keep finding information about the sp_addrole stored procedure which is the first step, but how do you go about defining what permissions this role has via T-SQL?
Thanks
View 3 Replies
View Related
Jun 5, 2000
Can anyone out there help me write a sp to determine if a user has a certain role?
I'm trying to use "sp_helpuser @UserName".
Can I declare a cursor with "EXEC sp_helpuser @UserName"? I'm not having success with this.
What about SELECT [GroupName] FROM EXEC sp_helpuser @UserName WHERE [GroupName] = @GroupName? Again, syntax error.
Is there already a better way that someone knows of?
View 1 Replies
View Related
Feb 14, 2008
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied.
how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database.
Thanks,
View 6 Replies
View Related
Jul 20, 2007
I use the following script in order to create db role:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [public]GO
It doesn't work:
Msg 15405, Level 16, State 1, Line 1
Cannot use the special principal 'public'.
However this code works fine:
USE [MyDB]GOCREATE ROLE [myRole] AUTHORIZATION [dbo]GOALTER AUTHORIZATION ON ROLE::[myRole] TO [public]GO
So the question is why?
View 6 Replies
View Related
Mar 22, 2007
Hi,
I want my application to create database and I do the following things:
1)Create application role
2)Grant create database to application role
3)Activate application role
4)Create database
and I get the answer:
CREATE DATABASE permission denied in database 'master'.
View 1 Replies
View Related
May 14, 2008
NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.
View 10 Replies
View Related
Oct 21, 2005
Hi, I have got a problem. When I try to access my database table Users, I get the following error:
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
So
I tried to grand this select command in MS Web Data Administration, but
it doesnt work. When I try to grand db_datareader role to dbo, I get
the following error
[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
View 1 Replies
View Related
Jun 8, 2001
Hi All,
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
TIA,
Shamil
View 4 Replies
View Related
Jul 7, 2004
Hi,
I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.
Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?
I have researched SQL-DMO, but didn't find what I'm looking for.
Any idea ?
Mike
View 1 Replies
View Related
Sep 21, 2006
Anyone have a tsql query that will give me a listing of database roles and their users already put together?
View 1 Replies
View Related
May 4, 2004
Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.
Greetings,
Godofredo
View 2 Replies
View Related
Jul 23, 2005
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
View 2 Replies
View Related
Aug 29, 2005
I wish to create a user that can backup any or all databases in our SQLServer 2000 Instance. I thought there would be a server role for thisfunction, however I can only find that after I grant access of adatabase to the user, then I can choose ds_backupoperator.I want to create a user that will have the ability to backup all thedatabases. I dont wish to have to come back to the server after a newtable is created and add the backup user to that table.I want SA w/o the full privilage...am I crazy?Any Suggestions?TIARobBackgroup: We currently have about 10 SQL servers, and adding more inthe future. I am using SQLBackup from Idera along with HP SurestoreTape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) withArcServe from Computer Associates. I want to have this automated tobackup to file then tape, regardless of what databases get created.
View 1 Replies
View Related
May 31, 2006
I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.
View 4 Replies
View Related
Jul 14, 2006
Okay I figured out how to determine if stored procs and funcs exist before dropping them.
How do I do the same for ROLE, LOGIN, USER?
I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.
Server 2005 and Server Express 2005
Thanks
View 3 Replies
View Related