Can Sp_grantdbaccess Attach User To Default Schema
Jun 15, 2007
Hi
For MSSQL 2005 server, when we create new user using stored procedure sp_grantdbaccess, it creates schema with name given to user.
While when we create new user with query " Create login identfied by'password'. It attach user to default schema at the server.
My question is can we have a default schema assigned to the user, while user is created with sp_grantdbaccess?
Is there any way to do so?
Please reply as early as possible.
View 3 Replies
ADVERTISEMENT
Apr 20, 2013
i have database and set default table schema to "ray" and me must input ruy.TABLE-NAME for retrive data !!! , i need set Default Schema to current user for call database as just database name(for my program) , how changed it ? (i change default schema for current user by alter command but not worked !)
View 1 Replies
View Related
May 6, 2008
Dear All,
I am using SQLServer 2005, I have setup a login user "User1" and next I setup database user using the same username and login name. After that I create a new schema "mySchema" and make "User1" as the owner of the schema. To "User1" I assigned the default schema to "mySchema", so far its working fine. But when I open the user's property window (dialogbox) the default schema always gets reset to "dbo".
What could be the problem here? Please help me if there is any solution to get the right schema which I assigned to the user.
Thanks and regards,
View 4 Replies
View Related
Nov 16, 2007
i have a stored procedure for creating SQL server users within a database but i have been given a change request where the created SQLServer user will need to be granteddbaccess to another db also
is there a way to do this??
at the mo my sp reads like this:-
Code Block
CREATE PROCEDURE ys_InsertUser2
@int_Identity int OUTPUT,
@str_username varchar(25),
@str_role varchar(20),
@str_password varchar(15),
@str_Database varchar(20)
AS
SET NOCOUNT ON
DECLARE @USER varchar(25)
SELECT @USER=username from tblUser where username=@str_username
BEGIN
if not exists (select * from master..syslogins where name = @str_username)
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)
select @logindb = @str_Database, @loginlang = N'British'
if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin @str_username, null, @logindb, @loginlang
END
if not exists (select * from sysusers where name = @str_username and uid < 16382)
EXEC sp_grantdbaccess @str_username, @str_username
exec sp_addrolemember @str_Role, @str_username
EXEC sp_password NULL, @str_password, @str_username
END
SELECT @int_Identity = 0 --just a bluff inorder to use formclasses
IF @@Error = 0
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 99
END
SET NOCOUNT OFF
but i need to expand the sp_grantdbaccess and sp_addrolemember to include access to another DB and not the local one which the stored proc is running in.
is this possible?
Cheers,
Craig
View 3 Replies
View Related
Sep 27, 2007
==SQL Server 2005 SP2==Is it possible to set the default schema in code?I know that, for a particular DB user, I can set the default schemastatically in Mgmt Studio. However, I want to do this dynamicallyin source code.I am using JDBC if that matters.Any help appreciated.TIAaj
View 1 Replies
View Related
May 22, 2008
I Would like to create a schema sample and make it as default schema instead of dbo.
If a user logs in and creates a table like create table t1 (no int ) .
it would be assigned to sample schema and displayed as sample.t1 not dbo.t1.
How to set the user created schema as primary schema.
View 9 Replies
View Related
May 4, 2006
We have a new development SQL2005 server.
To implement secutiry on this new server we decided to grant rights to NT-groups.
We defined an NT-group Programmersmembers and gave this group the necessary rights.
The problem now is when a member of this NT-group creates a table (this group is dbo in the development database) everything works fine as expected.
But when a member of this group tries to make a new table through 'Microsoft SQL server management studio' they receive the error that there is no default schema available.
But it seems to be impossible to map a default schema to an NT-Group.
=> Does this means we have no other way than to give each individual programmer the necessary rights and assign them to a default schema.
It is no option to force our programmers to create each table through a script on the development database.
Or is there another option ?
View 7 Replies
View Related
Sep 25, 2007
Hi,
Re: sql server 2005
I recently migrated a database from one server to another. In the process of doing this, I renamed one of the sql server 2000 migrated logins to a new name, using "alter login with name" and "alter user with name"
Now, I'm having problems with permissions. This user cannot execute sp_send_dbmail, even though it has explicit execute permissions on this stored proc in the msdb database.
What I noticed about this user is that is carried over a default schema from the old 2000 server. I'm beginning to think that this has something to do with the lost permissions. So I tried to drop the default schema. No dice. Next, I set the default schema to dbo. That still didn't work.
Can someone please tell me how to drop a default schema for a user? I've tried everything I can think of, from removing the schema name from the properties windows, to "alter user". Nothing has worked.
Thanks
View 3 Replies
View Related
Mar 31, 2006
During deployment of my database project I receive the following error:
"The default schema does not exist."
What could be causing this error?
View 1 Replies
View Related
Mar 14, 2006
I am confused.
I added my NT account to the sql server logins for my sql server (2005), then I added a corresponding user account to my database. I then set my default schema. I connect to the database, and the default schema seems to be set to dbo.
Can anyone thing of a reason why this might be happening? Is there some sort of override if I have additional privledges on the server?
I appreciate any thoughts...
-Mike Graham
View 4 Replies
View Related
Sep 5, 2007
Hi,
I have SQL Server Management Studio and SQL Server 2000.
What I want to know is:
Is there any way to prevent displaying default schema or owner, when viwing the "tables list" in the object browser?
eg. Instead of
dbo.MyTable1
dbo.MyTable2
....
I want to view as
MyTable1
MyTable2
Thanks
Srinika
View 5 Replies
View Related
Aug 28, 2005
We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
View 89 Replies
View Related
Sep 25, 2007
I'm migrating a dotnetnuke website from SQL Server 2000 to SQL Server 2005 and have run into a problem with one of the stored procedures.
The database objects seem to have upgraded successfully to use the db schema identifer from the dbowner identifier. However I am having a problem with a particular stored procedure trying to execute another stored procedure.
When the following procedure is called, it seems that the db engine has forgotten the schema context and therefore can't find the called procedure. Has anyone come across this before and is there a workaround other than modifing every SP that uses EXEC?
ALTER PROCEDURE [myschema].[dnn_Forum_StatisticsGet]
(
@ModuleID int,
@UpdateWindow int = 12,
@TabId int
)
...
BEGIN
EXEC dnn_Forum_AA_StatisticsSiteUpdate 0, 0, @ModuleID, @TabId
END
...
View 6 Replies
View Related
Dec 14, 2007
Does anyone know how to tell sp_rename to look in a schema other than the default.
The code below reproduces the problem.
-- WORKS IN default schema
--
create table dbo.TestDF1(
dfField intconstraint DF1 default 0
)
go
sp_rename 'DF1', 'DF2', 'OBJECT'
go
Select name
From
sys.default_constraints
where
object_name(parent_object_id) = 'TestDF1'
go
drop table dbo.TestDF1
go
-- DOESN'T WORK IN added schema
--
create schema TestSchema
go
create table TestSchema.TestDF2(
dfField intconstraint DF3 default 0
)
go
sp_rename 'DF3', 'DF4', 'OBJECT'
go
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315
Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong.
---------------------------
drop table TestSchema.TestDF2
go
drop schema TestSchema
go
View 2 Replies
View Related
Mar 3, 2008
Cannot open user default database. Login failed.Login failed for user
'NT AUTHORITYNETWORK SERVICE'. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Cannot open user default database. Login
failed.Login failed for user 'NT AUTHORITYNETWORK SERVICE'. I have This Error When i try to log into My online web site, i have no idea how to fix it,one day it was working and the next it wasnt, is there any way to find out what database the default is and if it's either incorrect or not present change the web.config in a way that will make my system work. i have the NT Authority/Network Service in my Server Properties Permissions, its given the type login and is granted Connect SQL by sa i have 3 colder copies of the web site on my server my question is, how would i use of of these to restore the original site configuration is there a way to restore the original configuration to undo whatever it is i've done to break the system ChrisStressed
View 3 Replies
View Related
Apr 25, 2007
Hi, I'm new to SQL Express 2005.
I found information regarding : "Cannot open user default database. Login failed."on this forum but I think that in my case it's a bit different issue.
I have a website (ASP.NET 2.0) accessing DB, in the mean time Windows Service tries to update some data in the same DB (Service runs as NT AUTHORITYLOCAL SYSTEM). The second connection is rejected: "Cannot open user default database. Login failed.Login failed for user ....".
Problem occurs only when both: service and website are running at the same time. So service and website are running without problems when they are connecting DB exclusively.
My connection string is:
"Data Source=.SQLEXPRESS;AttachDbFilename="|DataDirectory|spider-lab.mdf";Integrated Security=True;User Instance=True"
I would be grateful if you can help me
View 1 Replies
View Related
Jul 21, 2015
Running a report on sqlserver RS 2008 R2 that uses a data source that looks at a database on a sql express 2012 server.
I can run the report in preview mode from bits on the sql 2008 r2 server.
I have tested the connection of the deployed data source on the sql 2008 R2 reporting services web page and connection has been successful.
I can logon to sql express 2012 using management studio and logon as the user and access the database run stored procedures etc.
windows server 2003, sql server 2008 R2 reporting services server.
windows server 2012 sql express 2012 data source database location.
View 13 Replies
View Related
Jan 15, 2004
Hi,
I'm sorry if this is simple, I'm no DBA but have been tasked with solving this problem...
We have a website that connects via ODBC to SQL Server (2k sp1) and at the moment I am getting back about every other time:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open user default database. Login failed.
So this is NOT happening every single time. Now I have seen Microsoft KB - 307864 and I can see that none of the databases are marked as suspect, the database I am trying to connect to does exist and is attached and, I have run the command to switch the database to multi-user mode.
The probable cause of this problem is that a while ago we had a hard-drive failure and I was forced to reattach some old datafiles (mdf,ldf) as the database. This seemed ok and I can view data etc in enterprise manager no problem.
I have checked for orphaned users and the user I am logging in with from the webpage is not listed.
So does anyone have a clue as to why this is happening, and more frustratingly for me, why is it only happening some of the time.
Thanks for your help, appreciated.
James.
View 9 Replies
View Related
Jan 30, 2000
I have similar question like Tim asked but from a different angle. I wish I pose my question here too anybody can help answer this.
I tried to use detach and attach method to do backup of the user database (not master, maybe we can do master too) on another server, but it looks the login and permission credential would not go together. I come to see different kind of message I tried to login as non-sa user after I attatch the database on different machine. I would be denied for SQL timeout. It looks like loginand user dose not exist in new server because I did not move master database too. But even if I recreate the login when I tried to associate user with login I will see a message saying user or role already exist in database. If I go back to check the user it did not show up on enterprize manager on that database.
So my question would be
1). how to move login and user together with user database.
2) do we have to move master database where the login infor. reside
3). if so how to move master database from one server to another when other server already have master database exist?
Hope somebody can answer this.
Wislon
View 2 Replies
View Related
Nov 22, 2007
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
View 3 Replies
View Related
Jun 28, 2015
In some our dotabases I can see Schemas created with the same name as Domain User name (domainusername). Schema owner for those schemas is not dbo but the same user as in schema name. How this happens? Is any way to prevent or prohibit this?
View 9 Replies
View Related
Aug 22, 2006
We are using SQL Server2005 on Windows XP.
My question is:
What is the difference b/w schema, owner and user in SQL Server 2005. The reason for asking is that when I login as a user say 'user1' and create objects under it.. they should show up in its own schema - right. Because this is how Oracle works. Now we are not seeing this behaviour in SQL Server2005. Instead, we see the object created with <dbo>.<object_name>.
Are we missing something? thanks.
View 4 Replies
View Related
May 18, 2007
Is it possible to set up the permissions to not allow a specific user to create schemas, but to allow that user to create tables and procedures and functions in one schema, and to create procedures and functions but not tables, in a different schema within the same database?
View 3 Replies
View Related
Sep 23, 2015
I have created a user Finance and I want to grant him access only to see views which are created under Schema called "FinanceQuery".
Note: View may use tables from multiple schemas example: dbo. Staging. ect
By doing this, I want to achieve that this user Finance can see only Views created under Schema FinanceQuery and should not see any other objects (tables, Stored Procedures, Functions etc.)
View 3 Replies
View Related
Aug 8, 2006
I have granted execute on the dbo schema to a sql user so that he can execute available procedures in the dbo schema. However when he executes a procedure ie..
exec dbo.myproc
The following error is returned:
Msg 229, Level 14, State 5, Line 2
SELECT permission denied on object 'MyType', database 'Mine', schema 'dbo'.
MyType is a table
How can I correct this behavior?
I don't want the user to be able to access the tables except via the procedure calls.
View 5 Replies
View Related
Oct 22, 2015
I need to provide a User with below permissions:
1. Ability to read and write records in tables in both the X schema and Y schema
2. Ability to read metadata about objects in the X and Y schema
3. Ability to execute stored procedures in the X and Y schema
4. Ability to create and update the necessary schema objects used by X, including but not limited to tables, views, and indexes
5. CREATE FUNCTION permission
6. ALTER and EXECUTE permissions on the X schema
7. VIEW DEFINITION permission on the X and Y schemas to enable view export.
For the point 1, I will assign db_datareader,db_datawriter database roles to the user
For the point 2, when I have searched web, I found out ReadDefinition permission should be granted. I could find only viewDefinition but not ReadDefinition.
For the point 3, 'USE DataBaseName GRANT EXECUTE TO User; Go' - does this sql suffice?
For the point 4, I am not sure what should be done.
For the point 5, 'USE DataBaseName GRANT CREATE FUNCTION TO User; Go' - I guess this will work
For the point 6, Can I use same SQL as point 3 including ALTER ?
For the point 7, 'USE DataBaseName GRANT VIEW DEFINITION TO User; Go'
View 5 Replies
View Related
Oct 1, 2007
Hi all,
I am trying to establish a "sandbox" database for a group of users/developers whereby each user has their own schema and complete control over their schema and only their schema.
I began by creating user logins (Windows Authentication), created schemas for each user where the corresponding login is the owner, and set the schema as default for the user.
At this point, the logins only had "public" and could not create tables. I then granted the Create Table privilege which allowed them to create a table in their schema. However, they could also create tables anywhere else in the database including another user's schema.
How can I set up an enironment where each user has control over just their schema? What permissions would I need to grant and at what level (database,schema, etc.). I also need them to be able to grant privileges on their own schema and/or schema objects.
Any help would be greatly appreciated.
View 4 Replies
View Related
Nov 21, 2007
Hi all,
I just found that the content of my Database "ssmsExpressDB" is gone, but the name "ssmsExpressDB" remains in the Object Explorer of SQL Server Management Studio Express. If I delected the name "ssmsExpressDB" and executed the following .sql:
exec sp_attach_db @dbname = N'ssmsExpressDB',
@filename1 = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf',
@filename2 = N'C:Program filesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB_log.LDF'
GO
I got the following error message:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
And I have closed all my projects and I do not know what " The process cannot access the file because it is being used by another process" is all about!? Please help and tell me how I can re-attach the content of my "ssmsExpressDB" in the Object Explorer of SQL Server Management Studio Express.
Thanks in advance,
Scott Chang
====================================================================================
I found the "ssmsExpressDB" is being used by my VB 2005 Express project "Hello-SQLCLR-1": in the Database Explorer, Data Connections place. How can I put it back to the Object Explorer of SQL Server Management Studio Express? Please help and advise.
=======================================================
View 6 Replies
View Related
Oct 13, 2006
do the following steps:1:Use Manage Studio login the server with Integrated security.2:Create a dabase named testdb;3:Create a SQL Server login named amber ,and set it to be dbowner oftestdb;4:Create a SQL Server login named guxiaobo ,set it's default databse tobe testbd,and in the testdb databse map login guxiaobo to userguxiaobo;5:Close Manage Studio and reopen it ,this time use login amber log tothe server.6:In database testbd create a databse role role1 owned by dbo;7:In database testdb create a schema schema1 owned by dbo;8:Set user guxiaobo to be member of role1,and set guxiaobo's defaultschema to be schema1;9:In the schema properties-schema1 dialog choose permissions,in theusers or roles listview I add role1 to it.in the explicit permissionsfor role1 listbox,I choose select /update/insert/delete/view definitiongrant checkboxes.and apply the selects.10:In the schema1 schema I create a table:create table a(a int ,bvarchar(10));11:In the dabase role properties-role1 dialog choose securables panel,int securable listview I add the schema1.a table( choose all thecolumns of table a for all permissions),and in explicit permissions forschema1.a I choose all for grant ,then apply the selects.12:Now I use login guxiaobo to log into dabase and issue "select * froma",but got a error msg saying guxiaobo has not enough permission toselect form table a.Does anyone has found anything I missed?
View 6 Replies
View Related
Nov 22, 2006
Hi,
SQL Server Security is not my strong point so forgive me for asking stupid questions.
I have a bunch of tables and sprocs within a schema 'MySchema'. I have a user 'MyUser' defined in the database.
I would like to give MyUser permission to SELECT from tables and EXECUTE all sprocs in MySchema. What is the simplest way of doing that? Will the following:
GRANT EXECUTE ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION AS [db_owner]
GRANT SELECT ON SCHEMA::[MySchema] TO [MyUser] WITH GRANT OPTION
accomplish that? (I can't test it out at the moment because our DBA isn't around and I don't have permission)
With best practices in mind - is what I am doing here considered "ok". Any suggestions/comments are welcome.
-Jamie
P.S. Can anyone recommend any documentation that talks about what best practices should be in the use of schemas. BOL is a bit sparse. Thanks.
View 5 Replies
View Related
Jul 8, 2015
I just recently updated to SSDT 12.0.50512.0 using Visual Studio 2013 Ultimate. I typically use SSDT Schema Compare to synchronize my schema across multiple databases and different environments. After updating i encountered a major bug while updating our production schema.Typically during schema compare, the compare will prompt me to drop users and user roles from the database as they are not present in the project. I will exclude these so they database users and their roles aren't affected. After the update to SSDT I noticed that schema compare was only prompting me to drop the User, but didn't show anything about the user's roles. Not thinking much of it I went through my usual task of updating all the production databases. I soon found out that this did in fact remove the user roles even though it showed NOTHING in the schema compare UI indicating it would do so.
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datareader', @membername = N'dbuser';
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datawriter', @membername = N'dbuser';
You could say this is partially my fault for not checking the generated script before running it, but after months of this routine task I've never had an issue until this update.i'm not seeing the changes that will happen to my user roles in the schema compare UI?
View 2 Replies
View Related
Nov 12, 2014
Query to find the date/time when a database schema was created and who created it.
View 2 Replies
View Related
May 20, 2015
I created a new login and then created a new user [COM] in DB with default schema pointing to [COM]
I created then schema [COM] WITH AUTHORIZATION [COM]
I want this [COM] user to have all permissions it needs on [COM] schema only. How do I do that? When I try to create table [Com].Table it gives me permission denied.
What am I missing?
View 9 Replies
View Related
